MYSQL

MYSQL

Starting MySQL
You have to start the MySQL process before you can create your databases. To configure MySQL to start at boot time, use the chkconfig command:
[root@bigboy tmp]# chkconfig mysqld on
You can start, stop, and restart MySQL after boot time using the service commands.
[root@bigboy tmp]# service mysqld start
[root@bigboy tmp]# service mysqld stop
[root@bigboy tmp]# service mysqld restart
Remember to restart the mysqld process every time you make a change to the configuration file for the changes to take effect on the running process.
You can test whether the mysqld process is running with
[root@bigboy tmp]# pgrep mysqld
You should get a response of plain old process ID numbers.
The /etc/my.cnf File
The /etc/my.cnf file is the main MySQL configuration file. It sets the default MySQL database location and other parameters. The typical home/SOHO user won't need to edit this file at all.
The Location of MySQL Databases
According to the /etc/my.cnf file, MySQL databases are usually located in a subdirectory of the /var/lib/mysql/ directory. If you create a database named test, then the database files will be located in the directory /var/lib/mysql/test.
Creating a MySQL "root" Account
MySQL stores all its username and password data in a special database named mysql. You can add users to this database and specify the databases to which they will have access with the grant command. The MySQL root or superuser account, which is used to create and delete databases, is the exception. You need to use the mysqladmin command to set your root password. Only two steps are necessary for a brand new MySQL installation.
1. Make sure MySQL is started.
2. Use the mysqladmin command to set the MySQL root password. The syntax is as follows:
[root@tmp bigboy]# mysqladmin -u root password new-password

If you want to change your password later, you will probably have to do a root password recovery.
Accessing The MySQL Command Line
MySQL has its own command line interpreter (CLI). You need to know how to access it to do very basic administration.
You can access the MySQL CLI using the mysql command followed by the -u option for the username and -p, which tells MySQL to prompt for a password. Here user root gains access:
[root@bigboy tmp]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14 to server version: 3.23.58

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>
Note: Almost all MySQL CLI commands need to end with a semi-colon. Even the exit command used to get back to the Linux prompt needs one too!
Creating and Deleting MySQL Databases
Many Linux applications that use MySQL databases require you to create the database beforehand using the name of your choice. The procedure is relatively simple: Enter the MySQL CLI, and use the create database command:
mysql> create database salesdata;
Query OK, 1 row affected (0.00 sec)

mysql>
If you make a mistake during the installation process and need to delete the database, use the drop database command. The example deletes the newly created database named salesdata.
mysql> drop database salesdata;
Query OK, 0 rows affected (0.00 sec)

mysql>
Note: Sometimes a dropped database may still appear listed when you use the show databases command explained further below. This may happen even if your root user has been granted full privileges to the database, and it is usually caused by the presence of residual database files in your database directory. In such a case you may have to physically delete the database sub-directory in /var/lib/mysql from the Linux command line. Make sure you stop MySQL before you do this.
[root@bigboy tmp]# service mysqld stop

Granting Privileges to Users
On many occasions you will not only have to create a database, but also have to create a MySQL username and password with privileges to access the database. It is not a good idea to use the root account to do this because of its universal privileges.
MySQL stores all its username and password data in a special database named mysql. You can add users to this database and specify the databases to which they will have access with the grant command, which has the syntax.
sql> grant all privileges on database.* to username@"servername" identified by 'password';
So you can create a user named mysqluser with a password of pinksl1p to have full access to the database named salesdata on the local server (localhost) with the grant

command. If the database application's client resides on another server, then you'll want to replace the localhost address with the actual IP address of that client.
sql> grant all privileges on salesdata.* to mysqluser@"localhost" identified by 'pinksl1p';
The next step is to write the privilege changes to the mysql.sql database using the flush privileges command.
sql> flush privileges;

CREATE DATABASE

• 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;)
• 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
• 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

• 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)

• Quit from the SQL shell:
[prompt]$ quit

• 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


Viewing Your New MySQL Databases
A number of commands can provide information about your newly created database. Here are some examples:
• Login As The Database User: It is best to do all your database testing as the MySQL user you want the application to eventually use. This will make your testing mimic the actions of the application and results in better testing in a more production-like environment than using the root account.
[root@bigboy tmp]# mysql -u mysqluser -p salesdata
• List all your MySQL databases: The show databases command gives you a list of all your available MySQL databases. In the example, you can see that the salesdata database has been successfully created:
mysql> show databases;
+-----------+
| Database |
+-----------+
| salesdata |
+-----------+
1 row in set (0.00 sec)

mysql>
Listing The Data Tables In Your MySQL Database
The show tables command gives you a list of all the tables in your MySQL database, but you have to use the use command first to tell MySQL to which database it should apply the show tables command.
The example uses the salesdata database; notice that it has a table named test.
mysql> use salesdata;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------+
| Tables_in_salesdata |
+---------------------+
| test |
+---------------------+
1 row in set (0.00 sec)

mysql>
Viewing Your MySQL Database's Table Structure
The describe command gives you a list of all the data fields used in your database table. In the example, you can see that the table named test in the salesdata database keeps track of four fields: name, description, num, and date_modified.
mysql> describe test;
+---------------+--------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+------------+----------------+
| num | int(11) | | PRI | NULL | auto_increment |
| date_modified | date | | MUL | 0000-00-00 | |
| name | varchar(50) | | MUL | | |
| description | varchar(75) | YES | | NULL | |
+---------------+--------------+------+-----+------------+----------------+
6 rows in set (0.00 sec)

mysql>
Viewing The Contents Of A Table
You can view all the data contained in the table named test by using the select command. In this example you want to see all the data contained in the very first row in the table.
mysql> select * from test limit 1;
With a brand new database this will give a blank listing, but once the application starts and you enter data, you may want to run this command again as a rudimentary database sanity check.

Recovering / Changing Your MySQL Root Password
Sometimes you may have to recover the MySQL root password because it was either forgotten or misplaced. The steps you need are:
1) Stop MySQL
[root@bigboy tmp]# service mysqld stop
Stopping MySQL: [ OK ]
[root@bigboy tmp]#
2) Start MySQL in Safe mode with the mysqld_safe command and tell it not to read the grant tables with all the MySQL database passwords.
[root@bigboy tmp]# mysqld_safe --skip-grant-tables --skip-networking &
[1] 13007
[root@bigboy tmp]# Starting mysqld daemon with databases from /var/lib/mysql
[root@bigboy tmp]#
Note: In Fedora Core 3 and earlier the mysqld_safe command was named safe_mysqld and the general procedure for password recovery was different.
3) MySQL is now running without password protection. You now have to use the familiar mysql -u root command to get the mysql> command prompt. ( -p flag is not required) As expected, you will not be prompted for a password.
[root@bigboy tmp]# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.16

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>
4) You will now have to use the mysql database which contains the passwords for all the databases on your system and modify the root password. In this case we are setting it to ack33nsaltf1sh.
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> UPDATE user SET Password=PASSWORD("ack33nsaltf1sh") WHERE User="root";
Query OK, 1 row affected (0.00 sec)
Rows matched: 2 Changed: 1 Warnings: 0

mysql>
5) Exit MySQL and restart the mysqld daemon.
mysql> exit
Bye
[root@bigboy tmp]# service mysqld restart
STOPPING server from pid file /var/run/mysqld/mysqld.pid
051224 17:24:56 mysqld ended

Stopping MySQL: [ OK ]
Starting MySQL: [ OK ]
[1]+ Done mysqld_safe --skip-grant-tables --skip-networking
[root@bigboy tmp]#
The MySQL root user will now be able to manage MySQL using this new password.

MySQL Database Backup
The syntax for backing up a MySQL database is as follows:
mysqldump --add-drop-table -u [username] -p[password] [database] > [backup_file]
In the previous section, you gave user mysqluser full access to the salesdata database when mysqluser used the password pinksl1p. You can now back up this database to a single file called /tmp/salesdata-backup.sql with the command
[root@bigboy tmp]# mysqldump --add-drop-table -u mysqluser \
-ppinksl1p salesdata > /tmp/salesdata-backup.sql
Make sure there are no spaces between the -p switch and the password or else you may get syntax errors.
Note: Always backup the database named mysql too, because it contains all the database user access information.
Eg:
mysqldump --opt server4sale > /tmp/server4sale.sql

MySQL Database Restoration
The syntax for restoring a MySQL database is:
mysql -u [username] -p[password] [database] < [backup_file]
So, using the previous example, you can restore the contents of the database with
[root@bigboy tmp]# mysql -u mysqluser -ppinksl1p salesdata \
< /tmp/salesdata-backup.sql
Note: You may have to restore the database named mysql also, because it contains all the database user access information.

Eg:
mysql -u root -p server4sale < /tmp/server4sale.sql

MySQL Table Backup and Restoration
Sometimes you may want to backup only one or more tables from a database. There are some practical reasons for wanting to do this. You may have a message board / forums application that uses MySQL to store its data and you want to create a brand new forum with the same users as the old one so that the users don't have to register all over again.
The MySQL SELECT statement can be used to export the data to a backup file and the LOAD command can be used to import the data back into the new database used by the new forum. In this example the data in the phpbb_users and phpbb_themes tables of the forums-db-old database are exported to files named /tmp/forums-db-users.sql and /tmp/forums-db-themes.sql respectively. The data is then imported into tables of the same name in the forums-db-new database.
mysql> use forums-db-old;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT * INTO OUTFILE '/tmp/forums-db-users.sql' FROM phpbb_users;
Query OK, 1042 rows affected (0.03 sec)

mysql> SELECT * INTO OUTFILE '/tmp/forums-db-themes.sql' FROM phpbb_themes;
Query OK, 1038 rows affected (0.03 sec)

mysql> use forums-db-new;
Database changed
mysql> load data infile '/tmp/forums-db-users.sql' replace into table forums-db.phpbb_users ;
Query OK, 1042 rows affected (0.06 sec)
Records: 1042 Deleted: 0 Skipped: 0 Warnings: 0

mysql> load data infile '/tmp/forums-db-themes.sql' replace into table forums-db.phpbb_themes ;
Query OK, 1038 rows affected (0.04 sec)
Records: 1038 Deleted: 0 Skipped: 0 Warnings: 0

mysql>
As you can see, the syntax is fairly easy to understand. The REPLACE directive will overwrite any previously existing records with the same unique, or primary, key in the source and destination tables. The IGNORE directive will only insert records where the primary keys are different.