mysql 主从同步实验
【实验环境】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# vim /etc/my.cnf server-id=1 // 指定ID，这个ID必须唯一log-bin=mysql-bin //开启log- bin二进制日志文件 注: 里面默认有server-id=1，需要去掉这行，否则在实验过程中，经常会因为忽略了这一行的存在而报server-id 相同的错误。3、重启mysql# 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)备份# mysqldump -uroot -p rep > /usr/local/mysql/rep.sql(4)解锁表mysql> unlock tables;Query OK, 0 rows affected (0.00 sec)二、拷贝数据在同步之前，需要保证两台服务器上的数据库是一致的，如果数据库都是新装的，则可以跳过这一步(1)将主服务器上的备份文件拷贝到从服务器上# 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)复制 # mysql < /usr/local/mysql/rep.sql如果备份的时候遇到这样的问题：
# 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
解决办法:
# mysqldump -uroot -p --all-databases --skip-lock-tables > /usr/local/mysql/rep1.sql
三、配置slave
1、配置my.cnf# 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# 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: 664State: 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: 60retries: 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的选项，不允许插入操作。
页:
[1]