MySQL主从介绍、搭建、管理等详细过程

1、参考文早 1: 参考文章 2： 参考文章 3: 1 ,主从复制架构演示图170 thread该过程的第一部分就是 master记录二进制日志。在每个事务更新数据完成之前，master在二日志记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。下一步就是slave将master的bi nary log拷贝到它自己的中继日志。首先， slave开始一 个工作线程 I/O线程。I/O线程在master上打开一个普通的连接，然后开始 binlog dump process。Binlog dump proces

2、s从 master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待 master产生新的事件。I/O线程将这些事件写入中 继日志。SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件， 并重放其中的事件而更新slave的数据，使其与 master中的数 据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。此外，在 master中也有一个工作线程：和其它MySQL的连接一样，slave在master中打开一个连接也会使得master开始一个线程。复制过程有一个很重要的限制复制在slave上是

3、串行化的，也就是说master上的并行更新操作不能在slave上并行操作。2 , mysql主从同步应用场景(1)：数据分布(2)：负载均衡(3)：备份(4)：高可用和容错3，搭建mysql主从的环境要求主从系统要保持一致：包括数据库版本，操作系统版本，磁盘io磁盘容量，网络带宽等。rootdata02 # cat /etc/redhat-releaseCen tOS release 6.5 (Fin al)rootdata02 #主库master从库slaveOS系统版本Cen tOS release 6.2 (Fin al)Cen tOS release 6.2 (Fin al)数据库版本

4、5.6.12-log5.6.12-log磁盘容量50G30G主机ip地址2930端口33063306内存2G2G服务器类型虚拟机虚拟机MySQL 安装参考：http:/blog.csd n.n et/mchdba/article/details/511380634，开始搭建mysql主从复制4.1建立复制账号GRANT REPLICATIONSLAVE,RELOAD,SUPEON *.* TO repl'30' IDENTIFIED BY 'repl_1234：建立一个复制账号，只允许从192.16

5、8.52.130上来访问登录主库进行二进制日志传输同步。PS:如果 mysql版本新旧密码算法不同， 可以设置 set password for 'backup''00'=old_password('1234')4.2手动同步数据因为当开始搭建的时候，主库上已经有了数据， 所以要先把主库已经存在的数据先手动同步迁移到从库上面去。搭建过程中，禁止在主库从库上进行任何对数据库的ddl、dml等数据操作。这里可以用mysqldump也可以用xtrabackup导出主库上面的数据：(421): xtrabackup 方式在主库上192

6、.168.52.129上面进行数据备份，备份命令，要添加-safe-slave-backup参数：inn obackupex-user=backup-password="123456"-host=29-socket=/tmp/mysql.sock-defaults-file=/etc/my.c nf/data/backups/mysql/repl/backup_slave-parallel=3 -safe-slave-backup -no-timestamp去备份目录/data/backups/mysql/repl/backup_slave 查看备份

7、时候的主库二进制信息，需要根据这个二进制信息来进行数据同步，如下所示：rootdata01 test# cd /data/backups/mysql/repl/backup_slaverootdata01 backup_slave#more xtrabackup_b inlog_infomysql-bi n.000147120rootdata01 backup_slave#压缩备份文件并且传输到从库佃30上面：tar -zcvf backup_slave.tar.gz backup_slave/scp backup_slave.tar.gz 30:/

8、tmp/(422)mysqldump 方式在主库上佃29上做基于主库做数据备份/usr/local/mysql/bi n/mysqldump-ubackup -password=123456-host=29-sin gle-tra nsacti on -flush-logs -master-data=2 -add-drop-table -create-opti on -quick -exte nded-i nsert=false -set-charset -disable-keys -A > /tmp/alldb.sql压缩备份文件，传输到从库

9、佃30上面去。gzip /tmp/alldb.sqlscp /tmp/alldb.sql.gz 30:/tmp/4.3 配置 master 库(29)# Server ID# No sync databasesvim /etc/my.c nfserver-id=129log-bi n =/home/data/mysql/bi nlog/mysql-bin binl og-ig no re-db=mysql# No sync databases# No sync databasesbinlog-ignore-db=test bi

10、nlog-ignore-db=information_schema binlog-ignore-db=performance_schema binlog-do-db=user_db保存退出，重启 mysql 主库，可以查看主库的状态，如下： mysql> show master status;+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB| Executed_Gtid_Set |+| mysql-bin.000151 | 120 | user_db | mysql,test,information_schema,performan

11、ce_schema | |+1 row in set (0.00 sec) mysql>mysql> show master statusG;*1. row*File: mysql-bin.000151Position: 120Binlog_Do_DB: user_dbBinlog_Ignore_DB: mysql,test,information_schema,performance_schema Executed_Gtid_Set:1 row in set (0.00 sec)ERROR:No query specified mysql>4.4 配置 slave 从库(1

12、30 )Slave 的配置与 master 库类似，如下所示：Vim /etc/f#Master-Slave configlog-slave-updates=1replicate-same-server-id=0server-id=230 # Server ID log-bin =/home/data/mysql/binlog/mysql-bin.log relay-log=mysql-relay-bin# No sync databases# No sync databases# No sync databasesexpire-logs-days=10master-in

13、fo-repository=TABLE relay-log-info-repository=TABLE binlog-ignore-db=mysql binlog-ignore-db=test binlog-ignore-db=information_schema binlog-ignore-db=performance_schema binlog-do-db=user_dbserver_id 是必须的，而且唯一。 slave 没有必要开启二进制日志，但是在一些情况下，必须 设置，例如，如果 slave为其它slave的master，必须设置bin_log。在这里，我们开启了二进制日志，而且显

14、示的命名（默认名称为host name,但是，如果 host name改变则会出现问题）。relay_log 配置中继日志, log_slave_updates 表示 slave 将复制事件写进自己的二进制日志（后面会看到它的用处 ）。有些人开启了 slave的二进制日志，却没有设置 log_slave_updates，然后查看slave的数据 是否改变，这是一种错误的配置。所以，尽量 使用read_only，它防止改变数据（除了特殊 的线程）。但是，read_only并是很实用，特别是那些需要在 slave上创建表的应用。配置玩，重启 slave 数据库mysql> show slav

15、e status;Empty set （0.05 sec）mysql> 没有记录，需要设置一些主从配置。4.5 设置主从连接复制生成CHANGE MASTER语句，然后在从上执行，master信息，从备份集合里面获取：xtrabackup备份的话，从 xtrabackup_binlognfo里面获取，如下所示： rootdata02 tmp#tar -xvf backup_slave.tar.gzrootdata02 tmp#cd backup_slaverootdata02 backup_slave# more xtrabackup_binlog_infomysql-bin.00014

16、1 120rootdata02 backup_slave#Mysqldump的啊，从sql文件的头几列获取，如下所示：rootdata02 tmp#cd /tmp/rootdata02 tmp#gunzip alldb.sql.gzrootdata02 tmp# more alldb.sql |grep "CHANGE MASTER TO MASTER_LOG_FILE"|grep "MASTER_LOG_POS" |more- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000141', MA

17、STER_LOG_POS=120; 生成 change master 语句，如下：CHANGE MASTER TO MASTER_HOST='29', MASTER_USER='repl',MASTER_PASSWORD='repl_1234', MASTER_LOG_FILE='mysql-bin.000141',MASTER_LOG_POS=120;有报错信息：mysql> CHANGE MASTER TO MASTER_HOST='29',-> MA

18、STER_USER='repl',-> MASTER_PASSWORD='repl_1234',-> MASTER_LOG_FILE='mysql-bin.000141',-> MASTER_LOG_POS=120;ERROR 1794 (HY000): Slave is not configured or failed to initialize properly. You must at least set -server-id to enable either a master or a slave. Additional

19、 error messages can be found intheMySQL error log.mysql>具体原因目前不详， 网上查找到的资料 :数据库打开这几张表的默认引擎为MyISAM ，但是这几张表在建表时的引擎为 INNODB但是能确定的，这几张表确实是在 mysql5.6 中新入的innodb_index_stats,innodb_tables_stats,slave_master_info,slave_relay_log_info,slave_worker_info解决方法：(1) 登录数据库，进入mysql库，执行如下SQL删除5张表记住，一定要是 drop tabl

20、e if existsdrop table if exists innodb_index_stats; drop table if exists innodb_table_stats;drop table if exists slave_master_info; drop table if exists slave_relay_log_info; drop table if exists slave_worker_info;执行完后，可以用 show tables 查看一下，看表的数据是否已经比删除之前减少了，如果减 少了，说明你成功了！rootdata02 test cd /home/dat

21、a/mysql/data/mysqlrootdata02 mysql# ll *.ibd-rw-rw.1mysql mysql 983041?9 02:08 innodb_index_stats.ibd-rw-rw.1mysql mysql 983041?9 02:08 innodb_table_stats.ibd-rw-rw.1mysql mysql 983041?9 02:08 slave_master_info.ibd-rw-rw.1mysql mysql 983041?9 02:08 slave_relay_log_info.ibd-rw-rw.1mysql mysql 983041?

22、9 02:08 slave_worker_info.ibdrootdata02 mysql#强行删除 ibd 文件：rootdata02 mysql# rm -f *.ibd重启数据库，登录 mysqlsource /usr/test/mysql/share/mysql_system_tables.sqlshow tables;发现表已经回来了，表数据大概总数量为 28 个。之后执行 change master to , OK,搞定，如下所示：mysql> stop slave;Query OK, 0 rows affected, 1 warning (0.03 sec)mysql>

23、;CHANGEMASTERTOMASTER_HOST='29',MASTER_USER='repl',MASTER_PASSWORD='repl_1234',MASTE R_LOG_FILE='mysql-bin.000141', MASTER_LOG_POS=120;Query OK, 0 rows affected, 2 warnings (0.07 sec)mysql>启动 slavemysql> start slave;Query OK, 0 rows affected (0.02 se

24、c)mysql>4.6 验证主从复制状态在slave服务器上查看 slave状态:mysql> show slave statusGSlave_IO_State: Waiting for master to send event Master_Host: 29 Master_User: repl Master_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000151 Read_Master_Log_Pos: 346Relay_Log_File: mysql-relay-bin.000018Rel

25、ay_Log_Pos: 509Relay_Master_Log_File: mysql-bin.000151Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: business_db,user_db,plocc_systemReplicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0E

26、xec_Master_Log_Pos: 346Relay_Log_Space: 845Until_Condition: None这里主要看：Slave_IO_Running: YesSlave_SQL_Running: YesSeconds_Behind_Master: 010和SQL线程都是 Yes以及Seconds_Behind_Master是0就表示从库正常运行了。在 master 服务器上查看： mysql> show full processlist;+| db | Command| Time | State| Id | User| Host| Info|+|1 | event

27、_scheduler | localhostempty queue| 21 | root| localhost| NULL | Daemon| NULL | Query| 5874 | Waiting on| NULL|0 | init| show full processlist | 24 | repl | 30:45665 | NULL | Binlog Dump | 88 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL|+3 rows in set (0.

28、03 sec)mysql>看到有 30:45665 的线程在同步二进制数据4.7 master 添加数据验证去 master (29)上操作，添加表记录： mysql> create table master_test select 1 as a,'a' as b; Query OK, 1 row affected (0.72 sec)Records: 1 Duplicates: 0 Warnings: 0mysql>去 slave(30 )上查询下，看表数据是否同步过来，看到数据已经同步

29、过来了， 如下所示：mysql> select * from user_db.master_test;+-+-+| a | b |+-+-+| 1 | a |+-+-+1 row in set (0.06 sec)mysql>5 ，如何添加新的 slave 服务器总结如果master运行很久了，需要添加新的 slave服务器，那么搭建新的 slave,此时，有几种 方法可以使 slave 从另一个服务开始，例如，从 master 拷贝数据，从另一个 slave 克隆，从 最近的备份开始一个 slave。Slave与master同步时，需要三样东西：(1) master 的某个时刻的

30、数据快照；(2) master 当前的日志文件、以及生成快照时的字节偏移。这两个值可以叫做日志文件坐标(log file coordinate)，因为它们确定了一个二进制日志的位置，你可以用SHOW MASTER STATUS命令找到日志文件的坐标；(3) master 的二进制日志文件。也可以通过以下几中方法来克隆一个slave：(1) 冷拷贝(cold copy)停止master，将master的文件拷贝到slave;然后重启 master。缺点很明显。(2) 热拷贝(warm copy)如果你仅使用MylSAM表，你可以使用 mysqlhotcopy拷贝，即使服务器正在运行。 如果有my

31、isam和innodb表，可以在业务低峰期用tar包的方式来热拷贝。(3) 使用 mysqldump使用mysqldump来得到一个数据快照可分为以下几步：<3.a>锁表：如果你还没有锁表，你应该对表加锁，防止其它连接修改数据库，否则，你得 到的数据可以是不一致的。如下：mysql> FLUSH TABLES WITH READ LOCK;<3.b>在另一个连接用 mysqldump创建一个你想进行复制的数据库的转储：见421小节<3.c>对表释放锁。mysql> UNLOCK TABLES;(4) 使用 xtrabackup使用xtraback

32、up得到数据快照，见 422小节6，如何实现MSS当设置log_slave_updates时，你可以让 slave扮演其它slave的master。此时，slave把SQL 线程执行的事件写进行自己的二进制日志(binary log)，然后，它的slave可以获取这些事件7，复制过滤复制过滤可以让你只复制服务器中的一部分数据，有两种复制过滤：在master上过滤二进制日志中的事件；在 slave上过滤中继日志中的事件。如下图7.png所示：SlaveMasteriepkare_do_db iepliiate_do_tabie 即 1 tatt_igncre_db ieplkate_igncre

33、_table ieplicate_rewpite_lb iepl tatcwid_do_ta t>e irpl icdte w I d tgim «讪忖亡hn lcg_(h_db:bhloj-切no杷8，常用的mysql主从拓扑结构复制的体系结构有以下一些基本原则：(1)每个slave只能有一个 master ；每个slave只能有一个唯一的服务器ID;(3) 每个master可以有很多slave；(4) 如果你设置 log_slave_updates， slave可以是其它 slave的 master，从而扩散 master 的更新。MySQL不支持多主服务器复制 (Mult

34、imaster Replication)即一个 slave可以有多个 master。但是，通过一些简单的组合，我们却可以建立灵活而强大的复制体系结构。9， mysql主从同步之间的管理介绍一下基本的 mysql主从的管理操作命令：9.1停止mysql从服务STOP SLAVE IO_THREAD; #停止 10 进程STOP SLAVE SQL_THREAD; #停止 SQL进程STOP SLAVE;#停止 10 和 SQL进程9.2开启mysql主从同步服务START SLAVE IO_THREAD; # 启动 10 进程START SLAVE SQL_THREAD#启动 SQL进程START SLAVE;#启动10进程和SQL进程9.3重置mysql主从同步RESET SLAVE;#用于让从属服务器忘记其在主服务器的二进制日志中的复制位置，它会删除masternfo和文件，以及所有的中继日志，并启动一个新的中继日志，当你不需要主从的时候可以在从上执行这个操作。不然以后