17.1 MySQL主从介绍 17.2 准备工作 17.3 配置主 17.4 配置从 17.5 测试主从同步 扩展: 有的同学,遇到主从不能正常同步,提示uuid相同的错误。这是因为克隆机器导致。 https://www.2cto.com/database/201412/364479.html 不停库不锁表在线主从配置 http://seanlook.com/2015/12/14/mysql-replicas/ 主从不同步 http://www.rfyy.net/archives/2309.html http://blog.51cto.com/storysky/259280 主主 关于 auto_increment https://blog.csdn.net/leshami/article/details/39779509 http://www.cnblogs.com/ygqygq2/p/6045279.html mysql-proxy 实现读写分离 http://blog.51cto.com/zzclinux/1980487 mysql-proxy类似的产品有: mycat 基于阿里的开源软件cobar,官网 www.mycat.io https://my.oschina.net/ruoli/blog/1789370 mycat实现分库分表 https://www.cnblogs.com/joylee/p/7513038.html atlas 出自于360,不维护不更新了 https://blog.csdn.net/AnPHPer/article/details/80566385 mysql环形主从 http://ask.apelearn.com/question/11437 mysql架构演变 http://www.aminglinux.com/bbs/thread-8025-1-1.html MHA架构 http://blog.51cto.com/xiaoshuaigege/2060768 比较复杂的mysql集群架构 http://ask.apelearn.com/question/17026
Mysql的 Replication 是一个异步的复制过程,从一个 Mysql instace(我们称之为 Master)复制到另一个 Mysql instance(我们称之 Slave)。在 Master 与 Slave 之间的实现整个复制过程主要由三个线程来完成,其中两个线程(Sql线程和IO线程)在 Slave 端,另外一个线程(IO线程)在 Master 端。 要实现 MySQL 的 Replication ,首先必须打开 Master 端的Binary Log(mysql-bin.xxxxxx)功能,否则无法实现。因为整个复制过程实际上就是Slave从Master端获取该日志然后再在自己身上完全 顺序的执行日志中所记录的各种操作。打开 MySQL 的 Binary Log 可以通过在启动 MySQL Server 的过程中使用 “—log-bin” 参数选项,或者在 my.cnf 配置文件中的 mysqld 参数组([mysqld]标识后的参数部分)增加 “log-bin” 参数项。
MySQL 复制的基本过程如下:
两台机器,都需要安装mysql 主:192.168.141.128 从:192.168.141.129
配置文件修改完成之后需要重新启动mysql服务
在/data/mysql 目录下可以查看到binlog文件和索引文件
由于主的机器上是空库,从上的机器有一个zrlog库是上篇blog的库,所以把从上的库拿到主的机器,然后把从上的库给删除。
若slave io及sql线程已经启动,需要先 stop slave
[root@linux-02 ~]# mysql -uroot -plinux002
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.6.43 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> start slave;
Query OK, 0 rows affected (0.04 sec)
mysql> show slave status/G;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/G' at line 1
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.141.128
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: linux001.000001
Read_Master_Log_Pos: 28423
Relay_Log_File: linux-02-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: linux001.000001
Slave_IO_Running: Connecting
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: 28423
Relay_Log_Space: 120
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2003
Last_IO_Error: error connecting to master 'repl@192.168.141.128:3306' - retry-time: 60 retries: 1
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 190529 05:23:45
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye
Slave_IO_Running: Connecting Slave_SQL_Running: Yes 这个状态是两个yes才对,所以我们需要关闭slave拍错,关闭两台机器的防火墙。然后再打开slave。
[root@linux-02 ~]# iptables -F
[root@linux-02 ~]# mysql -uroot -plinux002
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.6.43 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
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: 192.168.141.128
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: linux001.000001
Read_Master_Log_Pos: 28423
Relay_Log_File: linux-02-relay-bin.000003
Relay_Log_Pos: 282
Relay_Master_Log_File: linux001.000001
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: 28423
Relay_Log_Space: 620
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: 0
Master_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: 128
Master_UUID: 98df4929-6ff5-11e9-bcec-000c296d81cc
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
[root@linux-001 ~]# mysql -uroot -plinux002
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 5.6.43-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql>
[root@linux-02 tomcat]# mysql -uroot -plinux002
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.43 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.141.128
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: linux001.000003
Read_Master_Log_Pos: 120
Relay_Log_File: linux-02-relay-bin.000013
Relay_Log_Pos: 282
Relay_Master_Log_File: linux001.000003
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: 120
Relay_Log_Space: 458
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: 0
Master_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: 128
Master_UUID: 98df4929-6ff5-11e9-bcec-000c296d81cc
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
mysql>
提供一个参考文档:http://xtra.aminglinux.com
mysqldump对于导出几个G的数据库或几个表,还是不错的,速度并不慢。一旦数据量达到几十上百G, 无论是对原库的压力还是导出的性能,mysqldump就力不从心了。Percona-Xtrabackup备份工具, 是实现MySQL在线热备工作的不二选择,可进行全量、增量、单表备份和还原。
xtrabackup命令只支持InnoDB和XtraDB存储引擎的数据库非阻塞地备份, 而innobackupex通过perl封装了一层xtrabackup,对MyISAM的备份通过加表读锁的方式实现。
在CentOS7上这样安装percona-xtrabackup 安装yum源 rpm -ivh http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm yum install -y percona-xtrabackup-24 //yum安装2.4版本
用xtrabackup做全量备份的命令是: innobackupex --defaults-file=/etc/my.cnf --host=10.100.100.100 --port=3333 –user=bakuser --password=your_pass /data/backup/mysql
说明:在执行该备份操作之前,需要先创建一个用户bakuser(用户名自定义), 并授予reload, lock tables, replication client, process, super等权限。 备份数据将会放到/data/backup/mysql目录里面,自动生成一个以当前日期、时间为名字的目录,例如2018-10-15_03_00_01。
增量备份: innobackupex --host=10.100.100.100 --port=3333 --user=bakuser --password=your_pass –incremental /data/backup/mysql --incremental-basedir=/data/backup/mysql/last-backup-file
说明: –incremental :表示本次备份是一个增量备份(若针对的上次备份为一个全量备份,这里也可以认为是个差量备份) –incremental-basedir:指定本次增量备份针对的哪一个备份(可以是上个增量,也可以是上个全量) 增量备份可以一环扣一环,比如可以周一做一个全量备份,然后周二做周一的增量,周三做周二的增量。。。
恢复: 1)全量备份的恢复 预备恢复:innobackupex --apply-log /PATH/TO/BACKUP/dir-quan --user-memroy=2G 停止MySQL/MariaDB,将datadir目录里面的数据清空(或者挪走) 恢复:innobackupex --defaults-file=/etc/my.cnf --copy-back /PATH/TO/BACKUP/dir-quan 改权限:chown -R mysql:mysql /PATH/TO/datadir 启动MySQL/MariaDB
说明: –apply-log:指定全备文件路径 –use-memory=N:指定恢复数据时,使用内存大小,默认为100M,若备份数据量大且有足够的空闲内存时, 可以用来指定大小的内存来工作,单位可以使用G,M…。 –defaults-file: 指定配置文件路径,通过配置文件它会找到datadir在哪里
2)增量备份的恢复 停止服务 删除dataidir里面的数据 准备全量: innobackupex --apply-log --redo-only /PATH/TO/全量备份目录 准备增量1: innobackupex --apply-log --redo-only /PATH/TO/全量备份目录 --incremental-dir=/PATH/TO/第一次增量备份目录 准备增量2: innobackupex --apply-log --redo-only /PATH/TO/全量备份目录 --incremental-dir=/PATH/TO/第二次增量备份目录 … 准备最后一个增量: innobackupex --apply-log /PATH/TO/全量备份目录 --incremental-dir=/PATH/TO/最后一次增量备份目录 再次准备全量: innobackupex --apply-log /PATH/TO/全量备份目录 #(这次不用加–redo-only了)
恢复: innobackupex --copy-back /PATH/TO/全量备份目录 改权限:chown -R mysql:mysql /PATH/TO/datadir 启动MySQL/MariaDB
说明:–redo-only:表示进行准备(应用日志)工作时,只进行redo操作,只会重做已提交但未应用的事务, 不会回滚未提交的事务。原因是后面还有个增量备份,未提交的可能在后面增量备份时进行提交。 需要注意的是,最后一个增量备份不需要加这个选型。
MariaDB10.3.x及以上的版本用Percona XtraBackup工具会有问题。 原因可能是MariaDB10.3以上版本的redo日志格式和之前不同了。 解决方案是,使用mariabackup,它是MariaDB提供的一个开源工具,用于对InnoDB,Aria和MyISAM表进行物理在线备份。 这个工具是基于Percona的XtraBackup(版本2.3.8)的解决方案:
全量备份: mariabackup --defaults-file=/etc/my.cnf --backup --user=xxx --password=xxx --target-dir /data/backup/2019-02-21
全量恢复: 停止数据库 清空datadir mariabackup --prepare --target-dir /data/backup/2019-02-21/ mariabackup --copy-back --target-dir /data/backup/2019-02-21/ 改属主、属组 启动
增量备份: mariabackup --defaults-file=/etc/my.cnf --backup --user=xxx --password=xxx --target-dir /data/backup/2019-02-21 第一个增量:mariabackup --backup --user=xxx --password=xxx --target-dir /data/backup/2019-02-21_inc1 --incremental-basedir /data/backup/2019-02-21 第二个增量:mariabackup --backup --user=xxx --password=xxx --target-dir /data/backup/2019-02-21_inc2 --incremental-basedir /data/backup/2019-02-21_inc1
增量恢复: 先准备全备 mariabackup --prepare --target-dir /data/backup/2019-02-21 --apply-log-only 准备第一个增量: cd /data/backup mariabackup --prepare --target-dir ./2019-02-21 --incremental-dir ./2019-02-21_inc1 --apply-log-only 准备第二个增量: mariabackup --prepare --target-dir ./2019-02-21 --incremental-dir ./2019-02-21_inc2 --apply-log-only 恢复: mariabackup --copy-back --target-dir ./2019-02-21
备份脚本
#!/bin/bash
bakdir=/data/backup
d=`date +%F`
for db in bbs blog db2
do
mysqldump -uroot -paminglinux $db > $bakdir/$db\_$d.sql
done
cd $bakdir
gzip *_$d.sql
find ./ -name "*.gz" -mtime +30 |xargs rm