mysql数据库目录迁移

     mysql默认安装的情况下，相应的文件都安装在默认的目录下,要想把安装好的mysql迁移到其他目录,只需要配置好my.cnf文件,然后将原有的文件(数据文件,binlog文件,redolog文件等),迁移到配置文件指定的目录即可,如下是整个迁移的过程.

1.查看当前的my.cnf文件
mysql@node2 ~]$ more /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[mysqld]
character_set_server=utf8
datadir=/var/lib/mysqldata
max_heap_table_size=256M
tmp_table_size=256M
ft_min_word_len=2
log-bin=/var/lib/mysqldata/bin_log/mysql-bin
expire_logs_days=15

innodb_data_home_dir=
innodb_data_file_path=/var/lib/mysqldata/ibdata1:140M;/var/lib/mysqldata/ibdata2:2G;/home/mysql/ibdata/ibdata3:100M:autoextend

2.创建重新部署后设定的路径
datadir=/home/mysql_app/dbdata
socket=/home/mysql_app/dbdata/mysql.sock

innodb_data_home_dir=/home/mysql_app/pro_data
innodb_data_file_path=ibdata1:100M;ibdata2:100M;ibdata3:100M;ibdata4:100M:autoextend
innodb_log_group_home_dir=/home/mysql_app/redolog

log_bin=/home/mysql_app/mysqllog/binlog/binlog.bin
slow_query_log_file=/home/mysql_app/mysqllog/logfile/slow-query.log
log-error=/home/mysql_app/mysqllog/logfile/mysql-err.log

relay-log-index=/home/mysql_app/mysqllog/relay-log/slave-relay-bin.index
relay-log=/home/mysql_app/mysqllog/relay-log/relay-log

相应的创建如上的目录:
datadir=/db/mysql/dbdata
socket=/db/mysql/dbdata/mysql.sock
innodb_data_home_dir=/db/mysql/innodb/data
innodb_log_group_home_dir=/db/mysql/innodb/redolog
log_bin=/db/mysql/mysqllog/binlog/mysql-bin
slow_query_log_file=/db/mysql/mysqllog/logfile/slow-query.log
log-error=/db/mysql/mysqllog/logfile/mysql-err.log

relay-log-index=/db/mysql/mysqllog/relay-log/slave-relay-bin.index
relay-log=/db/mysql/mysqllog/relay-log/relay-log

mkdir -p /db/mysql/dbdata
mkdir -p /db/mysql/innodb/data
mkdir -p /db/mysql/innodb/redolog
mkdir -p /db/mysql/mysqllog/binlog
mkdir -p /db/mysql/mysqllog/logfile
mkdir -p /db/mysql/mysqllog/relay-log/relay-log

3.根据新部署的目录修改my.cnf配置文件
根据新创建的目录修改配置文件
datadir=/db/mysql/dbdata
socket=/db/mysql/dbdata/mysql.sock

innodb_file_per_table=1
innodb_data_home_dir=/db/mysql/innodb/data
innodb_data_file_path=ibdata1:140M;ibdata2:2G;ibdata3:100M:autoextend
innodb_log_group_home_dir=/db/mysql/innodb/redolog

log_bin=/db/mysql/mysqllog/binlog/mysql-bin
slow_query_log_file=/db/mysql/mysqllog/logfile/slow-query.log
log-error=/db/mysql/mysqllog/logfile/mysql-err.log

relay-log-index=/db/mysql/mysqllog/relay-log/slave-relay-bin.index
relay-log=/db/mysql/mysqllog/relay-log/relay-log

4.将相应的数据文件拷贝到新创建的目录
数据文件
[mysql@node2 ~]$ cd /var/lib/mysqldata
[mysql@node2 mysqldata]$ cp . -R /db/mysql/dbdata/

binlog日志文件
[mysql@node2 dbdata]$ cd bin_log
[mysql@node2 bin_log]$ mv *.* /db/mysql/mysqllog/binlog/

innodb数据文件
mv /db/mysql/dbdata/ibdata1 /db/mysql/innodb/data/
mv /db/mysql/dbdata/ibdata2 /db/mysql/innodb/data/
mv /home/mysql/ibdata/ibdata3 /db/mysql/innodb/data/

 

redolog日志文件

mv /db/mysql/dbdata/ib_logfile0 /db/mysql/innodb/redolog/
mv /db/mysql/dbdata/ib_logfile1 /db/mysql/innodb/redolog/

5.使用配置好的配置文件启动数据库
mysqld_safe --defaults-file=/db/mysql/config/my.cnf

整个迁移大功告成!!!

-- The End --