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

Source: Internet
Author: User

 

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.