Change the location of a linux MySQL database and change the Database Name

 

In Linux: the default data file storage directory of MySQL is/var/lib/mysql. I want to do two things today: Change the Database Name (three methods are tested), and change the directory location of the MySQL database. Database rename: 1. rename database zhoz_db to zhoz_db_bak
Mysql> rename database zhoz_db to zhoz_db_bak; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database test to test_bak 'at line 1: An error is reported. It seems that you can check it online in the mysql6 environment. The test environment is mysql5.
2. mv/var/lib/mysql/zhoz_db/var/lib/mysql/zhoz_db_bak is successfully executed and renamed. However, you can enter phpMyAdmin to issue the current database, but the table cannot be displayed. In fact, it is impossible to change the name of an operation independently. The corresponding table also needs to be modified. Of course, it is okay if only backup is used.
3. the best of both worlds: perform operations under phpMyAdmin: select the database to be renamed → click [operation] in the upper right corner → rename the database as "zhoz_db_bak" → execute the following command to change the directory of the MySQL database: the default data file storage directory of MySQL is/var/lib/mysql, and the target is moved to/home/zhozcom_data:
1. Create a data directory under the home Directory: cd/home mkdir zhozcom_data
2. Stop the MySQL service process: mysql-u root-p shutdown
3. Move the entire/var/lib/mysql directory to/home/zhozcom_data mv/var/lib/mysql/home/zhozcom_data/
In this way, the MySQL data file is moved to/home/zhozcom_data/mysql.
4. Find my. if the cnf configuration file does not exist in the/etc/directory. for the cnf configuration file, go to/usr/share/mysql/and find *. copy one of the cnf files to/etc/and change it to my. cnf. Command: [root @ test1 mysql] # cp/usr/share/mysql/my. cnf/etc/my. cnf
5. Edit the MySQL configuration file/etc/my. cnf to ensure that MySQL works properly, you must specify the location where the mysql. sock file is generated. Modify socket =/var/lib/mysql. sock value: socket =/home/zhozcom_data/mysql. the sock operation is as follows: [root @ zhoz etc] # vi my. cnf # The MySQL server [mysqld] port = 3306 # socket =/var/lib/mysql. sock (original content, usually used for security # comment this line) socket =/home/zhozcom_data/mysql. sock (add this line)
6. Modify the MySQL STARTUP script/etc/init. d/mysql modify MySQL STARTUP script/etc/init. d/mysql: Change datadir =/var/lib/mysql to the current path: datadir =/home/zhozcom_data/mysql. Method: [root @ zhoz etc] # vi/etc/init. d/mysql # datadir =/var/lib/mysql (comment this row) datadir =/home/data/mysql (add this row)
7. Restart MySQL service/etc/init. d/mysql start
Everything is okay. If not, view the permission: drwxr-xr-x Directory. Restarting the database is also required.