Change MySQL database storage path and port in Linux

Change MySQL database storage path and port in Linux

Suppose we change to the/opt/data directory to pour:

1, opt directory to establish the data directory

Mkdir/opt/data

2, stop the MySQL service process

Mysqladmin-u root-p shutdown

3. Move/var/lib/mysql Entire directory to/opt/data

mv/var/lib/mysql/opt/data/

This will be the MySQL data document moved to the/opt/data/mysql.

4, MY.CNF document configuration

The default installation my.cnf in the/etc directory, if it is the source package installation, you may not put the configuration file in this directory. Then we need to copy a

Cp/usr/share/mysql/my-medium.cnf/etc/my.cnf

I'm here for an example of the default location for RPM package installation.

5, edit the MySQL configuration document/ETC/MY.CNF

To ensure that MySQL works properly, you need to indicate where the Mysql.sock documentation is generated. Modify the value on the right side of the equal sign in Socket=/var/lib/mysql/mysql.sock line:/opt/mysql/mysql.sock. The operation is as follows:

Vi/etc/my.cnf
# The MySQL server
[Mysqld]
Port = 3306
#socket =/var/lib/mysql/mysql.sock (original content, in order to be more secure with "#" comment on this row)
Socket =/opt/data/mysql/mysql.sock (plus this row)

6, modify the MySQL startup script/etc/init.d/mysql

Finally, you need to modify the MySQL startup script/etc/init.d/mysql, in which datadir=/var/lib/mysql the path to the right of the equal sign is changed to your current storage path: Opt/data/mysql.

Note: Some versions also need to modify the directory location in the/usr/bin/mysqld_safe file.

Finally make a mysql.sock link:

Ln-s/opt/data/mysql/mysql.sock/var/lib/mysql/mysql.sock

This step is omitted when the client is connected, or it can be configured in MY.CNF.

7, restart the MySQL service

Chown-r mysql:mysql/opt/data/mysql/← Change the ownership of the database for MySQL
chmod 700/opt/data/mysql/test/← Change Database directory property to 700
chmod 660/opt/data/mysql/test/*← Change the properties of the data in the database to 660
/etc/init.d/mysql start

Changing the port, you only need to modify the contents of the port=3306 in the/etc/my.cnf file as the new port number. However, when the reboot is completed, sometimes it still fails to start.

MySQL Daemon failed to start.
Starting mysqld:
[FAILED]
View Log as:

[ERROR] Can ' t start server:bind on TCP/IP port:permission denied

You only need to run the following command:

Setenforce 0

This problem is due to a selinux firewall's permissions problem.

add: Windows Server modifies MySQL database location and Port

First of all, the MySQL service to stop first.

Two. Change the database store master path in the MySQL profile My.ini

Open the MySQL default installation folder C:\Program files\mysql\mysql Server 5.1 my.ini file, click "Edit" at the top of Notepad, "find", enter DataDir in the Find content, and click "Find Next" Go " Path to the database root store main Path parameter setting, find Datadir= "C:/Documents and Settings/all users/application data/mysql/mysql Server 5.1/data/"is the default database storage master path setting, now change it to the C:\mysql\data (you want) folder, and the correct setting is Datadir=" c:/mysql/data/".

Click "File" on the menu bar when the change is complete, and then click "Save".

Three. Copy the database files and folders from the old database storage main path to the new storage master path

Copy all files and folders in the C:/Documents and Settings/all users/application data/mysql/mysql Server 5.1/data/folder to your newly created folder directory.

Finally restarted.