MySQL搭建主从同步设置

竹子开花的时

主库：192.168.1.1
从库：192.168.1.2
数据文件存放位置：/var/mysql/data
待同步数据库：syncdb
 
一、设置MASTER
1、修改my.cnf
============================
# vi /etc/my.cnf
server-id = 1
log-bin
binlog-ignore-db=mysql
binlog-ignore-db=test
============================
2、赋予SLAVE权限帐号，允许用户在MASTER上LOAD TABLE和LOAD DATA
====================================================================
mysql> GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO slave@192.168.1.2 IDENTIFIED BY 'password';
====================================================================
3、锁主库表
===========================
mysql> FLUSH TABLES WITH READ LOCK;
===========================
4、显示主库信息
记录File和Position，从库设置将会用到
=====================
mysql> SHOW MASTER STATUS;
=====================
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_do_db | Binlog_ignore_db |
+---------------+----------+--------------+------------------+
| mysql-bin.030 | 870279   |              |                  |
+---------------+----------+--------------+------------------+
5、另开一个终端，打包主库
============================
# cd /var/mysql/data
# tar cvf syncdb.tar syncdb
============================
二、设置SLAVE
1、传输拿到主库包、解包
==============================
# cd /var/mysql/data
# scp 192.168.1.1:/var/mysql/data/syncdb.tar .
# tar xvf syncdb.tar
==============================
2、解锁主库表
=================
mysql> UNLOCK TABLES;
=================
3、查看修改syncdb文件夹权限
=====================
# chown mysql:mysql syncdb -R
=====================
4、修改my.cnf
====================
# vi /etc/my.cnf
# slave
server-id=2
master-host=192.168.1.1
master-user=slave
master-password=password
master-port=3306
replicate-do-db=syncdb
====================
5、验证连接MASTER
============================
# mysql -h192.168.1.1 -uslave -ppassword
mysql> show grants for slave@192.168.1.2;
============================
+------------------------------------------------------------------------------------------------------------------------+
| Grants for slave@192.168.1.2                                                                                           |
+------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, FILE, REPLICATION SLAVE ON *.* TO 'slave'@'192.168.1.2' IDENTIFIED BY PASSWORD '9ff2c222f44c7bba5cc7e3b' |
+------------------------------------------------------------------------------------------------------------------------+
6、在SLAVE上设置同步
设置连接MASTER MASTER_LOG_FILE为主库的File，MASTER_LOG_POS为主库的Position
============================
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.1',MASTER_USER='slave',MASTER_PASSWORD='password',MASTER_LOG_FILE='mysql-bin.030',MASTER_LOG_POS=870279;
============================
7、启动SLAVE服务
=============
mysql> slave start;
=============
8、查看SLAVE状态
=====================
mysql> SHOW SLAVE STATUS;
=====================
其中 Slave_IO_Running 和 Slave_SQL_Running 两列的值都为 "Yes"，表明 Slave 的 I/O 和 SQL 线程都在正常运行。
到此主从库搭建成功。

从库SLAVE启动问题
由于一些错误操作导致 CHANGE MASTER 和 SLAVE 服务无法启动，系统报错如下：
*****************************************************************
Could not initialize master info structure; more error messages can be found in the MySQL error log.
*****************************************************************
无法初始化master info结构，MySQL错误日志记录了更详细的错误信息。
解决方法：
1、查看MySQL错误日志，如：同步的上一个Position是多少，很多情况下无法启动服务是由于mysql识别的同步始终停留在上一个Position上。
2、查看master.info和relay-log.info，master.info 记录MASTER相关信息，relay-log.info 记录当前同步日志信息。
3、停止myslq服务，删除master.info和relay-log.info。
4、启动mysql服务。
5、重新CHANGE MASTER，重新启动SLAVE服务。