mysql迁移binlog_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 --