【MySql】主从复制

来源：互联网发布：域名第四级地址编辑：程序博客网时间：2024/06/06 12:28

http://blog.itpub.net/22664653/viewspace-713498/

MySQL支持单向、异步复制，复制过程中一个Linux服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护日志文件的一个索引以跟踪日志循环。当一个从服务器连接到主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知下一次更新。
为什么使用主从复制？
1、主服务器/从服务器设置增加了健壮性。主服务器出现问题时，你可以切换到从服务器作为备份。
2、通过在主服务器和从服务器之间切分处理客户查询的负荷，可以得到更好的客户响应时间。但是不要同时在主从服务器上进行更新，这样可能引起冲突。
3、使用复制的另一个好处是可以使用一个从服务器执行备份，而不会干扰主服务器。在备份过程中主服务器可以继续处理更新。
MySQL使用3个线程来执行复制功能(其中1个在主服务器上，另两个在从服务器上。当发出START SLAVE时，从服务器创建一个I/O线程，以连接主服务器并让主服务器发送二进制日志。主服务器创建一个线程将二进制日志中的内容发送到从服务器。从服务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件中，即中继日志。第3个线程是SQL线程，从服务器使用此线程读取中继日志并执行日志中包含的更新。SHOW PROCESSLIST语句可以查询在主服务器上和从服务器上发生的关于复制的信息。
默认中继日志使用host_name-relay-bin.nnnnnn形式的文件名，其中host_name是从服务器主机名，nnnnnn是序列号。用连续序列号来创建连续中继日志文件，从000001开始。从服务器跟踪中继日志索引文件来识别目前正使用的中继日志。默认中继日志索引文件名为host_name-relay-bin.index。在默认情况，这些文件在从服务器的数据目录中被创建。中继日志与二进制日志的格式相同，并且可以用MySQLbinlog读取。当SQL线程执行完中继日志中的所有事件后，中继日志将会被自动删除。
从服务器在数据目录中另外创建两个状态文件--master.info和relay-log.info。状态文件保存在硬盘上，从服务器关闭时不会丢失。
下次从服务器启动时，读取这些文件以确定它已经从主服务器读取了多少二进制日志，以及处理自己的中继日志的程度。
rac3 主库：10.250.7.241
rac4 备库：10.250.7.220
具体配置步骤：
1 修改主库和备库的my.cnf文件
主库my.cnf 与master-slave 相关的配置
server-id = 1
log-bin = master-bin
log-bin-index = master-log-bin.index
log-error = master-error.log
binlog_format = mixed
relay-log = slave-relay.log
relay-log-info-file = slave-relay-log.info
relay-log-index = slave-relay-log.index
备库my.cnf 与master-slave 相关的配置
server-id = 2
log-bin = master-bin
log-bin-index = master-log-bin.index
log-error = master-error.log
#中继日志名。默认名为host_name-relay-bin.nnnnnn，其中host_name是从服务器主机的名，nnnnnn表示中继日志在编号序列中创建。
relay-log = slave-relay.log
relay-log-info-file = slave-relay-log.info
relay-log-index = slave-relay-log.index

#启用从库日志，这样可以进行链式复制
log-slave-updates
#从库是否只读，0表示可读写，1表示只读
read-only=1
#只复制某个表
replicate-do-table=tablename
#只复制某些表（可用匹配符）
replicate-wild-do-table=tablename%
#只复制某个库
replicate-do-db=dbname
#只复制某些库
replicte-wild-do-db=dbname%
#不复制某个表
replicate-ignore-table=tablename
#不复制某些表
replicate-wild-ignore-table=tablename%
#不复制某个库
replicate-ignore-db=dbname
#复制完的sql语句是否立即从中继日志中清除，1表示立即清除
relay-log-purge=1
#从服务器主机，用于show slave hosts生成从库清单
report-host=hostname
2 在主库进行建立复制帐号，允许备库rac4能够读取复制主库的数据。
mysql> GRANT REPLICATION SLAVE ON *.* TO 'rep'@'10.250.7.220 ' IDENTIFIED BY 'rep123';
Query OK, 0 rows affected (0.03 sec)
mysql> GRANT REPLICATION CLIENT ON *.* TO 'rep'@'10.250.7.220 ';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SUPER ON *.* TO 'rep'@'10.250.7.220 ';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT RELOAD ON *.* TO 'rep'@'10.250.7.220 ';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SELECT ON *.* TO 'rep'@'10.250.7.220 ';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
3 锁住主库的table，清空所有表和块写入语句，制作主库的一致性快照，以便备份数据文件到从库进行初始化。
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 |
+-------------------+----------+--------------+------------------+
| master-bin.000001 | 822 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.13 sec)
4 保持锁表的客户端程序不退出，在另一个窗口对主服务器数据目录做快照。
[root@rac3 mysql]# tar zcvf data.tar.gz ./data
拷贝主库的数据文件到备库上。
5 在rac4上，启动从库：
mysql> CHANGE MASTER TO MASTER_HOST='10.250.7.241',
-> MASTER_USER ='rep',
-> MASTER_PASSWORD ='rep123',
-> MASTER_LOG_FILE ='master-bin.000001',
-> MASTER_LOG_POS =822;
启动slave 复制
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.250.7.241
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 65041
Relay_Log_File: slave-relay.000002
Relay_Log_Pos: 64473
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes --这两项都为yes的时候，表明slave正常运行。
Slave_SQL_Running: Yes
在主库上执行：
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
6 验证：
在主库：
mysql> use yang;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table yang as select * from test.t1;
mysql> show tables;
+----------------+
| Tables_in_yang |
+----------------+
| yang |
+----------------+
在备库验证：
mysql> use yang;
Database changed
mysql> show tables;
+----------------+
| Tables_in_yang |
+----------------+
| yang |
+----------------+
mysql> select count(1) from yang;
+----------+
| count(1) |
+----------+
| 1000 |
+----------+
主库执行：

mysql> insert into yang select * from test.sbtest;
Query OK, 1000000 rows affected (19.83 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
查看备库的show processlist，可以查看主库写表yang的时候，备库的进程的状态变化，
等待主库传送日志--解析日志，执行dml语句--继续等待主库日志的更新，这种方式和Oracle Streams的本质思想是一致的。
mysql> show processlist;
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+------------------+
| 2 | system user | | NULL | Connect | 88440 | Waiting for master to send event | NULL |
| 3 | system user | | NULL | Connect | 90 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 8 | root | localhost | yang | Query | 0 | NULL | show processlist |
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
mysql> show processlist;
+----+-------------+-----------+------+---------+-------+----------------------------------+---------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+-------+----------------------------------+---------------------------------------------+
| 2 | system user | | NULL | Connect | 88445 | Waiting for master to send event | NULL |
| 3 | system user | | yang | Connect | 24 | Sending data | insert into yang select * from test.sbtest |
| 8 | root | localhost | yang | Query | 0 | NULL | show processlist |
+----+-------------+-----------+------+---------+-------+----------------------------------+---------------------------------------------+
3 rows in set (0.00 sec)
mysql> show processlist;
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+------------------+
| 2 | system user | | NULL | Connect | 88459 | Waiting for master to send event | NULL |
| 3 | system user | | NULL | Connect | 38 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 8 | root | localhost | yang | Query | 0 | NULL | show processlist |
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
mysql> select count(1) from yang;
+----------+
| count(1) |
+----------+
| 1002000 |
+----------+
参考文章：
http://www.ningoo.net/html/2007/mysql_replication_configuration.html
http://www.oklinux.cn/html/sql/other/20080316/49421.html
http://www.cnblogs.com/rootq/archive/2008/11/11/1331267.html

0 0