MySQL Data migration method under CentOS

The first type:

The original database does not need to be reinstalled:

The default mysql will be installed in /var/lib/mysql Here, if the data is migrated to the /data/mysql directory, the steps are as follows:

1. stop MySQL service

2, #cp/var/lib/mysql/*/data/mysql/

#chown-R Mysql.mysql/data/mysql

3. Modify the my.cnf file

Basedir =/usr

DataDir =/data/mysql

Socket =/data/mysql/mysql.sock

Pid-file =/data/mysql/hostname.pid

if it is a third-party mysql-rpm package installation, the default profile is in the /usr/my.cnf directory

if yum is installed, the configuration file is /etc/my.cnf

4, modify the/etc/init.d/mysqld(yum installation with this,rpm third-party installation here to change / Etc/init.d/mysql) The datadir directory

5, after the migration mysql-u root-p is not in, need to reset the root password

#mysqld_safe--skip-grant-tables &

#mysql ( Open another window execution )

>use MySQL;

>update user Set Password=password (' New password ') where user= ' root '

>flush privileges;

6. Restart Service

#/etc/init.d/mysql start

at this point in the execution #mysql-uroot-p can go in, but show databases will first let set once password, Follow the prompts to do so.

Until this database migration is complete, the previous database can be operated properly.

The second Kind

The database needs to be reinstalled (must be the same version):

1, Reinstall after the default path or /var/lib/mysql, if you want to migrate to the /data directory, follow the first step to operate it.

2, the previous database recovery steps are as follows:

A) stop the MySQL service

b) Copy the previous /var/lib/msyql/order2017 to the /data/mysql directory. Modify Permissions chown-r mysql.mysql/data/mysql/order2017 This time the database and the table exist, but when viewing the data will be reported 1146 this error ; Continue with the following actions

c) Copy the previous /var/lib/mysql/ibdata1 cover /data/mysql/ibdata1

d) restart MySQL service

The previous database can also be accessed after reinstalling here

MySQL Data migration method under CentOS