mysql 主从同步实验

老爷子88

【实验环境】master    rhel6.3 （最小化）slave       rhel6.3 （最小化）IP分配：master     192.168.30.110slave        192.168.30.106一、配置Master1、Master 上为slave添加一个用户，并授权mysql> grant replication slave on *.* to 'rep'@'192.168.30.106' identified by '123456';Query OK, 0 rows affected (0.05 sec)2、编辑my.cnf[root@master ~]# vim /etc/my.cnf server-id=1             // 指定ID，这个ID必须唯一log-bin=mysql-bin        //开启log- bin二进制日志文件 注: [mysqld]里面默认有server-id=1，需要去掉这行，否则在实验过程中，经常会因为忽略了这一行的存在而报server-id 相同的错误。3、重启mysql[root@master ~]# service mysqld restart4、记录下二进制日志文件名以及位置        这里我们将模拟实际生产环境来进行实验，在AB同步之前需要先将主服务器备份给从服务器，以确保两台服务器的数据是一样的。        到这一步，我们需要记录下二进制日志文件的文件名以及位置，这样可以告诉从服务器同步的时候从哪个文件哪个位置开始同步，所以必须保证这个位置不变。但是备份的操作也会改变其位置，所以在做备份之前需要先锁表。(1)锁表mysql> flush tables with read lock;Query OK, 0 rows affected (0.01 sec)(2)记录二进制日志文件及日志mysql> show master status;+------------------+----------+--------------+------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000005 |      107 |              |                  |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)//  需要记录下二进制文件的名字和位置(3)备份[root@master ~]# mysqldump -uroot -p rep > /usr/local/mysql/rep.sql(4)解锁表mysql> unlock tables;Query OK, 0 rows affected (0.00 sec)二、拷贝数据在同步之前，需要保证两台服务器上的数据库是一致的，如果数据库都是新装的，则可以跳过这一步(1)将主服务器上的备份文件拷贝到从服务器上[root@master ~]# scp /usr/local/mysql/rep.sql root@192.168.30.106:/usr/local/mysql/root@192.168.30.106's password: rep.sql                                                                 100% 1259     1.2KB/s   00:00   (2)复制　　[root@slave ~]# mysql < /usr/local/mysql/rep.sql
　　如果备份的时候遇到这样的问题：
　　[root@master ~]# mysqldump -uroot -p --all-databases > /usr/local/mysql/rep1.sql
　　Enter password:
　　mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES
　　解决办法:
[root@master ~]# mysqldump -uroot -p --all-databases --skip-lock-tables > /usr/local/mysql/rep1.sql　　
　　三、配置slave
1、配置my.cnf[root@slave ~]# vim /etc/my.cnf server-id=2                // 设置server-id，不可重复log-bin=mysql-bin    // 开启二进制日志，用于链式复制的情况下，即这台服务器如果需要作为其他从服务器的主服务器，则需要开启这个选项read_only = 1                              // 设置为只读relay_log = mysql-relay-bin                //配置中继日志log_slave_updates = 1                            // 表示slave 将复制事件写进自己的二进制日志　　注：my.cnf 中默认有server-id=1这一行，可直接对这行修改或者去掉该行，自行添加一个唯一的id
2、重启mysql[root@slave ~]# service mysqld restartShutting MySQL... SUCCESS!　　Starting MySQL... SUCCESS!
3、对从数据库进行相应的设置mysql> change master to    -> master_host='192.168.30.110',    -> master_user='rep',    -> master_password='123456',    -> master_log_file='mysql-bin.000005',    -> master_log_pos=107;Query OK, 0 rows affected (0.06 sec)mysql> start slave;Query OK, 0 rows affected (0.03 sec)执行show processlist 查看进程mysql> show processlist\G*************************** 3. row ***************************     Id: 7   User: system user   Host:      db: NULLCommand: Connect   Time: 664  State: Slave has read all relay log; waiting for the slave I/O thread to update it   Info: NULL 看到以上信息，表示slave 已经连接到master，并开始接受并执行日志。mysql> show slave status\G;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.30.110                  Master_User: rep                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000005          Read_Master_Log_Pos: 267               Relay_Log_File: mysql-relay-bin.000002                Relay_Log_Pos: 413        Relay_Master_Log_File: mysql-bin.000005             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB:           Replicate_Ignore_DB:            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 0                   Last_Error:                  Skip_Counter: 0          Exec_Master_Log_Pos: 267              Relay_Log_Space: 569              Until_Condition: None               Until_Log_File:                 Until_Log_Pos: 0           Master_SSL_Allowed: No           Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 0               Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 1验证是否正常工作：mysql> show slave status\G;           Slave_IO_State: Waiting for master to send event      Slave_IO_Running: Yes           Slave_SQL_Running: Yes　　看到上面两个YES说明，IO以及SQL线程都已正常工作。
四、测试在master上面创建一个新的表mysql> use test;mysql> create table user(id int);mysql> show tables;+----------------+| Tables_in_test |+----------------+| user           |+----------------+在slave上查看同步情况，可以看到mysql> show tables;+----------------+| Tables_in_test |+----------------+| user           |+----------------+到此，mysql 的主从同步完成！========================实验可能遇到的问题1、在slave上，使用命令show slave status 看到如下报错The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave butthis does not always make sense; please check the manual before using it)解决办法：从上面的输出可以看到发送错误的原因是server-id重复了。检查主从上的server-id是否唯一（注意，my.cnf配置中默认已经有server-id=1）2、手动执行同步的时候，查看slave status 时，sql线程为no可能因为之前已经同步，再次执行会报错，因为之前的数据库可能已经存在，再次执行创建的sql语句，则会有错解决办法：之前的数据库数据，可以通过锁表，备份并恢复到从数据库上，然后重新定义新的二进制日志文件名以及位置，再次开启同步即可。（即跳过之前执行过的操作）3 查看master status 状态看到io线程没有正常工作，显示connecting Slave_IO_Running: Connecting            Slave_SQL_Running: YesLast_IO_Error: error reconnecting to master 'rep@192.168.122.224:3306' - retry-time: 60  retries: 86400从上面的信息可以看到IO线程连接超时，解决办法：(1) 查看防火墙是否放行3306(2) 注意mysql 修改了配置之后是否有重启，还有网络状态如何　　(3) 看看master主机IP地址是否写错
(4)主服务器上授权grant 语句有没写错（比如登陆的IP，用户名或者密码）========================其他1、主从服务器同步维护由于各种原因，导致主从数据不一致，在负载低的时候，进行手动同步.在主服务器上执行mysql> flush tables with read lock;Query OK, 0 rows affected (0.00 sec)mysql> show master status;+------------------+----------+--------------+------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000005 |      355 |              |                  |+------------------+----------+--------------+------------------+在从服务器上执行先得到当前主服务器的二进制文件名和偏移量,执行命令使从服务器与主服务器同步mysql> select master_pos_wait('mysql-bin.000005','355');+-------------------------------------------+| master_pos_wait('mysql-bin.000005','355') |+-------------------------------------------+|                                         0 |+-------------------------------------------+同步完成后，回到主服务器上，解锁mysql> unlock tables;=====================注意：read-only = 1 这个选项对于root用户无效，root用户照样可以执行插入更新的操作。mysql> insert into test1.test values ('2');Query OK, 1 row affected (0.06 sec)mysql> select * from test1.test;+------+| id   |+------+|    1 ||    2 |+------+这个选项只对普通用户生效:新添加一个用户并授权mysql> grant all privileges on test1.test to 'dbuser'@localhost identified by '123456';　　退出mysql后，使用dbuser这个刚刚新建的用户测试
mysql> use test1;　　Database changed
　　mysql> insert into test values('3');
　　ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
　　可以看到，提示mysql 设置了read-only的选项，不允许插入操作。