配置主库
更改my.cnf配置
vim /home/mysql_data/my.cnf
server-id = 30 #[必须]服务器唯一ID
log-bin = /home/log-bin/3306_log_bin #[必须]启用二进制日志
log-error = /home/mysql_log/3306_error_log.log #错误日志
重启mysql
/etc/init.d/mysql restart
创建用于同步的账号
grant replication slave on *.* to 'mysync'@'%' identified by '{马赛克}';
flush privileges;
mysql> use mysql;
mysql> select user,repl_slave_priv from user;
+--------+-----------------+
| user | repl_slave_priv |
+--------+-----------------+
| root | Y |
| root | Y |
| root | Y |
| root | Y |
| | N |
| | N |
| mysync | Y |
+--------+-----------------+
7 rows in set (0.00 sec)
查询master的状态
mysql> show master status;
+---------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| 3306_log_bin.000001 | 337 | | |
+---------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
配置从库
更改my.cnf配置
vim /home/mysql_data/my.cnf
server-id = 31 #[必须]服务器唯一ID
log-bin = /home/log-bin/3306_log_bin #[必须]启用二进制日志
log-error = /home/mysql_log/3306_error_log.log #错误日志
重启mysql
/etc/init.d/mysql restart
配置从服务器Slave
change master to master_host='10.199.100.25',master_user='mysync',master_password='{马赛克}',master_log_file='3306_log_bin.000001', master_log_pos=337;
start slave;
;如果mysqldump导出有使用--master-data=1参数,导出数据中会含有master_log_file、master_log_pos这时候则不需要在语句中带着两个参数;
检查从服务器复制功能状态
show slave status\G;
主要检查:
Slave_IO_Running=Yes
Slave_SQL_Running=Yes
判断主从延迟:
Seconds_Behind_Master: 0
更改mysql数据存储路径
service mysqld stop
mkdir /home/ssd/mysql_data
cp -R /home/mysql_data/* /home/ssd/mysql_data
chown -R mysql.mysql /home/ssd/mysql_data/
修改/etc/init.d/mysqld里面的datadir的指向
修改/home/ssd/mysql_data/my.cnf里面datadir
service mysqld start