configuring mysql after installation installing mysql linux apache
1. Admin user id: root
Default password: blank
The first task is to assign a password:
[prompt]$ mysqladmin -u root password ‘new-password’
Note: the following SQL commands will also work:
mysql> USE mysql;
mysql> UPDATE user SET Password=PASSWORD(‘new-password’) WHERE user=’root’;
mysql> FLUSH PRIVILEGES;
2. Create a database: (Creates directory /var/lib/mysql/bedrock)
[prompt]$ mysqladmin -h localhost -u root -ppassword create bedrock
(or use SQL command: CREATE DATABASE bedrock;)
3. Add tables, data, etc:
Connect to database and issue the following SQL commands:
[prompt]$ mysql -h localhost -u root -ppassword
…
mysql> use bedrock; – Define database to connect to. Refers to directory path: /var/lib/mysql/bedrock
mysql> create table employee (Name char(20),Dept char(20),jobTitle char(20));
mysql> DESCRIBE employee; – View the table just created. Same as “show columns from employee;”
+———-+———-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———-+———-+——+—–+———+——-+
| Name | char(20) | YES | | NULL | |
| Dept | char(20) | YES | | NULL | |
| jobTitle | char(20) | YES | | NULL | |
+———-+———-+——+—–+———+——-+
3 rows in set (0.03 sec)
mysql> show tables;
+——————-+
| Tables_in_bedrock |
+——————-+
| employee |
+——————-+
mysql> INSERT INTO employee VALUES (‘Fred Flinstone’,'Quarry Worker’,'Rock Digger’);
mysql> INSERT INTO employee VALUES (‘Wilma Flinstone’,'Finance’,'Analyst’);
mysql> INSERT into employee values (‘Barney Rubble’,'Sales’,'Neighbor’);
mysql> INSERT INTO employee VALUES (‘Betty Rubble’,'IT’,'Neighbor’);
Note: Data type used was CHAR. Other data types include:
* CHAR(M) : Fixed length string. Always stores M characters whether it is holding 2 or 20 characters. Where M can range 1 to 255 characters.
* VARCHAR(M) : Variable length. Stores only the string. If M is defined to be 200 but the string is 20 characters long, only 20 characters are stored. Slower than CHAR.
* INT : Ranging from -2147483648 to 2147483647 or unsigned 0 to 4294967295
* FLOAT(M,N) : FLOAT(4,2) – Four digits total of which 2 are after the decimal. i.e. 12.34 Values are rounded to fit format if they are too large.
* DATE, TEXT, BLOB, SET, ENUM
4. Add a user. Use the MySQL SQL console to enter SQL commands. The command mysql with the correct login/password will connect you to the database. The admin tables are stored in the database “mysql”.
[prompt]$ mysql -h localhost -u root -ppassword
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 3.23.41
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql> USE mysql;
mysql> SHOW TABLES;
+—————–+
| Tables_in_mysql |
+—————–+
| columns_priv |
| db |
| func |
| host |
| tables_priv |
| user |
+—————–+
mysql> INSERT INTO user (Host, User, Password, Select_priv) VALUES (”, ‘Dude1′, password(‘supersecret’), ‘Y’);
mysql> FLUSH PRIVILEGES; – Required each time one makes a change to the GRANT table
mysql> GRANT ALL PRIVILEGES ON bedrock.* TO Dude1;
mysql> FLUSH PRIVILEGES; – Required each time one makes a change to the GRANT table
mysql> quit
Note:
* There is NO space between the -p and the password! You can omit the password and you will be prompted for it.
* The SQL flush command is equivalent to issuing the command:
[prompt]$ mysqladmin reload
5. Test the database:
mysql> SELECT * from employee;
+—————–+—————+————-+
| Name | Dept | jobTitle |
+—————–+—————+————-+
| Fred Flinstone | Quarry Worker | Rock Digger |
| Wilma Flinstone | Finance | Analyst |
| Barney Rubble | Sales | Neighbor |
| Betty Rubble | IT | Neighbor |
+—————–+—————+————-+
1 row in set (0.00 sec)
mysql> SELECT name FROM employee WHERE dept=’Sales’;
+—————+
| name |
+—————+
| Barney Rubble |
+—————+
1 row in set (0.00 sec)
6. Quit from the SQL shell:
[prompt]$ quit
7. Shutting down the database:
[prompt]$ mysqladmin -u root -ppassword shutdown – PREFERRED
OR
[prompt]$ /etc/rc.d/init.d/mysqld stop
OR
[prompt]$ service mysqld stop
