mysql主从复制数据库配置及相关操作
- 什么是mysql的主从复制
MySQL主从复制也可以称为MySQL主从同步，它是构建数据库高可用集群架构的基础。它通过将一台主机的数据复制到其他一台或多台主机上，并重新应用relay log中的SQL语句来实现复制功能。MySQL支持单向、双向、链式级联、异步复制，5.5版本之后加入的半同步复制，5.6版本之后的GTID复制，MySQL5.7的多源复制、并行复制、loss-less复制。
2.常见的几种架构
1）单向主从模式：Master ——> Slave
2）双向主从模式：Master <====> Master
3）级联主从模式：Master ——> Slave1 ——> Slave2
4）一主多从模式
- 多主一从模式
3.主从复制功能
1）实时灾备
2）读写分离
3）高可用
4）从库数据统计
5）从库数据备份
6）平滑升级
4.主从复制原理
4.1异步复制
Master服务器
Slave服务
主从复制是异步复制过程
master开启bin-log功能，日志文件用于记录数据库的读写增删
需要开启3个线程，master IO线程，slave开启 IO线程 SQL线程，
Slave 通过IO线程连接master，并且请求某个bin-log，position之后的内容。
MASTER服务器收到slave IO线程发来的日志请求信息，io线程去将bin-log内容，position返回给slave IO线程。
slave服务器收到bin-log日志内容，将bin-log日志内容写入relay-log中继日志，创建一个master.info的文件，该文件记录了master ip 用户名 密码 master bin-log名称，bin-log position。
slave端开启SQL线程，实时监控relay-log日志内容是否有更新，解析文件中的SQL语句，在slave数据库中去执行。
4.2半同步复制
MySQL默认的复制方式是异步复制，但是当主库宕机，在高可用架构坐准备切换，就会造成新的主库丢失数据的现象。
MySQL5.5版本之后引入了半同步复制，但是主从服务器必须同时安装半同步复制插件。在该功能下，确保从库接收完成主库传递过来的binlog内容已经写入到自己的relay log后才会通知主库上面的等待线程。如果等待超时(超时参数：rpl_semi_sync_master_timeout)，则关闭半同步复制，并自动转换为异步复制模式，直到至少有一台从库通知主库已经接收到binlog信息为止。
半同步复制时，为了保证主库上的每一个Binlog事务都能够被可靠的复制到从库上，主库在每次事务成功提交时，并不及时反馈给前端应用用户，而是等待其中的一个从库也接收到Binlog事务并成功写入中继日志后，出库才返回commit操作成功给客户端。半同步复制保证了事务成功提交后，至少有两份日志记录，一份在主库的Binlog日志上，另一份在至少一个从库的中继日志Relay log上，从而更近一步保证了数据的完整性。
在这个半同步复制模式下：第1、2、3中任何一个步骤中主库宕机，则事务并没有提交成功。从库也没有得到日志，此时的主从复制数据是一致的。
半同步复制提升了主从之间数据的一致性，让复制更加安全可靠，在5.7 版本中又增加了rpl_semi_sync_master_wait_point参数，用来控制半同步模式下主库返回给session事务成功之前的事务提交方式。
4.3GTID复制（略，了解）
GTID又叫全局事务ID，是一个以提交事务的编号，并且是一个全局唯一的编号。GTID是由server_uuid和事务id组成的，即GTID=server_uuid:transaction_id。
server_uuid是数据库启动自动生成的，保存在auto.cnf文件下，transaction_id是事务提交时由系统顺序分配的一个不会重复的序列行。
GTID存在的价值：
1）GTID使用master_auto_position=1代替了基于binlog和position号的主从复制方式，更便于主从复制的搭建。
2）GTID可以知道事务在最开始是哪个实例上提交的。
3）GTID方便实现主从之间的failover，无须找position和binlog。
GTID限制条件：
1）不能使用create table table_name select * from table_name。
2）不支持CREATE TEMPORARY TABLE or DROP TEMPORARY TABLE语句操作。
3）不支持sql_slave_skip_counter。
5.主从搭建
mysql版本 5.5.56
5.1原理图
5.2准备实验环境
主(Master)机IP： 124.204.65.84:12485 （内网：172.16.120.10）
从(Slave) 机IP: 124.204.65.84:12493 （内网：172.16.120.11）
5.3配置主机master的my.conf文件
1.停止mysql /etc/init.d/mysqld stop
2.修改 vi /etc/my.conf
[mysqld]
server-id = 1 # 服务的唯一标识符
log-bin = mysql-bin # 开启二进制日志,默认路径在data/下
binlog_format = mixed # 二进制文件的格式
# 这里有３种格式
# binlog_format=statement 将主库输入的SQL语句,直接写入二进制文件中
# binlog_format=row 将主库修改的行写入二进制文件中
# binlog_format=mixed 以上二种的混合模式
# 在不同二进制格式下,对存储过程,函数,触发器,事件的数据复制可以参考
#下面参数,在本次实验中,不配置========================================
replicate-do-db = test # 需要复制的数据库 (在这个试验中,我不用这个参数)
replicate-ignore-db = mysql # 不需要复制的数据库 (在这个试验中,我不用这个参数)
replicate-ignore-db = information_schema # 不需要复制的数据库 (在这个试验中,我不用这个参数)
# 不使用replicate-do-db，replicate-ignore-db参数就代表全库都复制
# ===========================================================
如图：
3.启动master /etc/init.d/mysqld start
4.在master机器上创建远程登陆用户
用户名:slavec
密码:123456
允许来自：124.204.65.84 机器登陆
1.授权方式：此种授权是可远程登录Master，仅用于主从复制没有其它任何select insert update等权限
GRANT REPLICATION SLAVE on *.* to 'slavec'@'172.16.120.11' identified by ‘123456’;
FLUSH PRIVILEGES;
GRANT REPLICATION SLAVE ON *.* TO 'slavec'@'172.16.120.11' IDENTIFIED BY '123456' WITH GRANT OPTION;
查看授权结果
SHOW GRANTS FOR 'slavec'@'172.16.120.11';
- 测试从Slave端登录远程主(Master)机
mysql -uslavec -p123456 -h172.16.120.10
3. 从主(Master)机查看所有连接登录本机的客户端情况 SHOW PROCESSLIST ;
5.4修改slave从机my.conf文件
1.停止mysql服务 /etc/init.d/mysqld stop
2.修改 /etc/my.conf
[mysqld]
log-bin=mysql-bin # 确认也开启binlog日志
binlog_format=mixed # 确认binlog日志格式为mixd混合类型
server-id = 2 # 在整个主从架构内指定一个唯一的server-id，范围：1^32
如图：
启动mysql : /etc/init.d/mysqld start
5.4.2slave服务选配
从(Slave)机配置-【可选】
下面是生产阶段可能要用的功能，这些功能只能在从机的配置文件的[mysqld]区块里手工配置，无法通过 change master to ...命令实现。
1.白名单 - 只想复制某(些)个库：
replicate_do_db=zyyshop # 追加复制zyyshop库
replicate_do_db=test # 追加只复制test库
总结: 相当于只复制 zyyshop、test 两个库
如果采用完全在配置文件里配置主(Master)机信息的方法，此项须添加，否则可能导致mysqld启动不了
2.白名单 - 只想复制某(些)个库(的所有表)
replicate_wild_do_table=zyyshop.% # 追加复制zyyshop库的所有表
replicate_wild_do_table=test.% # 追加复制test库的所有表
总结：也是相当于只复制 zyyshop、test 两个库。
3.白名单 - 只想复制某(些)个库(的部分表)
replicate_wild_do_table=zyyshop.stu # 追加zyyshop.stu表
replicate_wild_do_table=test.project # 追加test.project表
总结：相当于只复制zyyshop.stu、test.project两个表。
4.黑名单 - 想忽略对mysql和test数据库的复制
replicate_ignore_db=mysql # 追加mysql库为忽略
replicate_ignore_db=test # 追加test库为忽略
总结：相当于将mysql和test两数据库追加到忽略列表，将不会被复制。
5.黑名单 - 想忽略对某些库的某些表的复制
replicate_wild_ignore_table=mysql.users # 追加mysql.users表为忽略
总结：本步骤是相当于追加了mysql.users具体的表被忽略，其它表照常会被复制。
6.如果想实现“主-->从-->从”的链式架构，因为在默认情况下，from主(Master)机copy过来的数据不会写入从(Slave)机的binlog日志里，
而是写入中继日志(localhost-relay-bin.00000x)里，所以本机如果还需要充当其它机器的主机，就将从主机复制来的数据写入从库时同
时也显式地写入本地binlog日志，添加如下配置：
log-slave-updates =1
5.5 slave连接master
5.5.1先查看当前主机二进制的位置
1．先查看当前主机二进制的位置show master status;
2.在slave从机上执行命令连接主机
CHANGE MASTER TO MASTER_HOST="172.16.120.10", MASTER_PORT=8764, MASTER_USER="slavec", MASTER_PASSWORD="123456", MASTER_LOG_FILE="mysql-bin.000004", MASTER_PORT=8764, MASTER_LOG_POS=336387, MASTER_CONNECT_RETRY=10;
- 备机上启动IO线程和SQL线程
START SLAVE;
4. 在备机上查看主从状态
SHOW SLAVE STATUS;
6.主主搭建
6.1原理图
说明：
Master-Master复制的两台服务器，既是master，又是另一台服务器的slave。这样，任何一方所做的变更，都会通过复制应用到另外一方的数据库中。
6.2准备环境
主(Master)机A IP： 124.204.65.84:12485 （内网：172.16.120.10）
主(Master)机B IP： 124.204.65.84: 12478 （内网：172.16.120.9）
6.3配置主机A
步骤1：
A服务器停掉mysql,修改my.conf service mysqld stop
vi /etc/my.conf
添加
#masterA自增长ID
auto_increment_offset = 1
auto_increment_increment = 2
log-slave-updates = true #将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启
配置完后启动service mysqld start
6.4配置主机B
步骤1：
A服务器停掉mysql,修改my.conf service mysqld stop
vi /etc/my.conf
添加
#masterA自增长ID
auto_increment_offset = 2
auto_increment_increment = 2
log-slave-updates = true #将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启
配置完后启动 service mysqld start
6.5查询A和B的status拿到bin-log和postion
分别查看A和B的 master的状态
SHOW MASTER STATUS;
A服务器:
B服务器:
给A添加B的同步用户信息
INSERT INTO mysql.user(HOST,USER,PASSWORD) VALUES('172.16.120.9','slaveg',PASSWORD('123456'));
-- 授权方式
GRANT REPLICATION SLAVE ON *.* TO 'slaveg'@'172.16.120.9' IDENTIFIED BY '123456' WITH GRANT OPTION;
FLUSH PRIVILEGES;
给B添加A的同步用户信息
INSERT INTO mysql.user(HOST,USER,PASSWORD) VALUES('172.16.120.10','slaveh',PASSWORD('123456'));
-- 授权方式
GRANT REPLICATION SLAVE ON *.* TO 'slaveh'@'172.16.120.10' IDENTIFIED BY '123456' WITH GRANT OPTION;
FLUSH PRIVILEGES;
在A上执行：
Stop slave;
CHANGE MASTER TO MASTER_HOST='172.16.120.9',MASTER_USER='slaveh',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000004',MASTER_PORT =8764,MASTER_LOG_POS=671;
Start slave;
在B上执行：
Stop slave;
CHANGE MASTER TO MASTER_HOST='172.16.120.10',MASTER_USER='slaveg',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000007',MASTER_PORT =8764,MASTER_LOG_POS=49466;
Start slave;
分别查询 A,B的 show slave status\G;
查询
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
是否都为yes
如图
7.主主从
7.1原理图
7.2准备环境
主A: 124.204.65.84:12485(内网172.16.120.10)
主B: 124.204.65.84:12478(内网172.16.120.9)
从C: 124.204.65.84:12496(内网172.16.120.12)
7.3配置主机A
步骤1：
A服务器停掉mysql,修改my.conf service mysqld stop
vi /etc/my.conf
添加
#masterA自增长ID
auto_increment_offset = 1
auto_increment_increment = 2
log-slave-updates = true #将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启
配置完后启动service mysqld start
7.4配置主机B
步骤1：
A服务器停掉mysql,修改my.conf service mysqld stop
vi /etc/my.conf
添加
#masterA自增长ID
auto_increment_offset = 2
auto_increment_increment = 2
log-slave-updates = true #将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启
配置完后启动 service mysqld start
7.5配置从机C
步骤1：
A服务器停掉mysql,修改my.conf service mysqld stop
vi /etc/my.conf
-- 配置
SERVER-id=12
LOG-BIN=mysql-BIN
binlog_format=mixed
-- etc/my.conf 配置不需要同步的数据
replicate_ignore_db=amc
replicate_ignore_db=amc_20171018
replicate_ignore_db=amc_20181108
replicate_ignore_db=amc_hbrb
replicate_ignore_db=db_mblog
replicate_ignore_db=hbcms
replicate_ignore_db=hbrb
replicate_ignore_db=hb_oa
replicate_ignore_db=healthmedia
replicate_ignore_db=mscms
replicate_ignore_db=sxxw
replicate_ignore_db=taiji_blog
replicate_ignore_db=amc_hbrbtest
replicate_ignore_db=amc_sxwb
replicate_ignore_db=mysql
replicate_ignore_db=performance_schema
replicate_ignore_db=shinyv_mms_jj
replicate_ignore_db=xuyong
replicate_ignore_db=information_schema
replicate_ignore_db=sys
7.6访问账号配置
A为B配置，B为A配置，同6（主主搭建）
重点：A为c配置
-- 10（主服务器）
-- 授权方式：此种授权是可远程登录Master，仅用于主从复制没有其它任何select insert update等权限
GRANT REPLICATION SLAVE ON *.* TO 'slavei'@'172.16.120.12' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;
GRANT REPLICATION SLAVE ON *.* TO 'slavei'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
-- 查看授权结果
SHOW GRANTS FOR 'slavei'@'172.16.120.12';
C执行
-- 查看10服务器的 show master status;
-- 同步master的bin-log和postion
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST="172.16.120.10", MASTER_PORT=8764,MASTER_DELAY =20, MASTER_USER="slavei", MASTER_PASSWORD="123456", MASTER_LOG_FILE="mysql-bin.000007", MASTER_LOG_POS=115601, MASTER_CONNECT_RETRY=10;
START SLAVE;
SHOW SLAVE STATUS;
一定要注意：slave_io_running slave_sql_running 都为yes
7.7延迟加载说明
配置参数： MASTER_DELAY =20
8. Mysql的容灾，备份操作
8.1mysqldump备份
8.1.1原理
实际上就是把数据库从mysql库里以sql语句的形式直接输出并保存成文件
备份的文件/* 到 */都是注释,其余都是sql语句
备份恢复就相当于把所有sql语句执行一遍
8.1.2用法
mysqldump -u username -p 数据库名 [表名]... > 文件路径
8.1.3用例
备份所有库并压缩：mysqldump -u root -p -A > xx.sql
备份某个库 mysqldump -u root -p -B DBNAME > xx.sql
备份多个库 mysqldump -u root -p -B DBNAME1 DBNAME2 > xx.sql
格式：mysqldump -h主机名 -P端口 -u用户名 -p密码 --database 数据库名 > 文件名.sql
例如： mysqldump -uroot -p tj_master_m > /home/chenjian/tj_master_m.sql
备份指定数据库指定表(多个表以空格间隔)
mysqldump -uroot -p mysql db event > /backup/mysqldump/2table.db
备份恢复 mysql -uroot -p < xxx.sql
soure方法
mysql > use db_name
mysql > source /backup/mysqldump/db_name.db
8.2利用集群热备份
以上主从，主主，主主从搭建环境即是。
8.3定时自动备份
8.3.1创建shell脚本
vi bkDatabaseName.sh
内容：
- #!/bin/bash
- mysqldump -uusername -ppassword DatabaseName > /home/dbback/DatabaseName_$(date +%Y%m%d_%H%M%S).sql
对备份进行压缩：
#!/bin/bashmysqldump -uusername -ppassword DatabaseName | gzip > /home/dbback/DatabaseName_$(date +%Y%m%d_%H%M%S).sql.gz
注意：
把 username 替换为实际的用户名；
把 password 替换为实际的密码；
把 DatabaseName 替换为实际的数据库名；
8.3.2添加可执行权限
chmod u+x bkDatabaseName.sh
执行：./bkDatabaseName.sh
8.4添加linux的计划任务
安装crontab （此次略）
8.5利用工具备份
附：mysql的安装
附.1版本
mysql-5.5.56-linux-glibc2.5-x86_64.tar.gz
附.2解压和安装
tar -zxvf mysql-5.5.56-linux-glibc2.5-x86_64.tar.gz
mv mysql /usr/local/mysql
cd /usr/local/mysql/
创建mysql用户组和mysql用户
sudo groupadd mysql
sudo useradd -r -g mysql mysql
关联myql用户到mysql用户组中
sudo chown -R mysql:mysql /usr/local/mysql/
sudo chown -R mysql:mysql /usr/local/mysql/data/
sudo chown -R mysql /usr/local/mysql/
sudo chown -R mysql /usr/local/mysql/data/
sudo chown -R mysql:mysql /usr/local/mysql
更改mysql安装文件夹mysql/的权限
sudo chmod -R 755 /usr/local/mysql/
安装libaio依赖包
yum install libaio
初始化mysql
cd /usr/local/mysql/bin/
sudo /usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data/ --basedir=/usr/local/mysql/
sudo ./mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --initialize
启动mysql服务
sh /usr/local/mysql/support-files/mysql.server start
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chmod 755 /etc/init.d/mysqld
修改my.cnf文件
vi /etc/my.cnf
为以下配置
[client]
character-set-server = utf8
port = 12584
socket = /usr/local/mysql/mysql.sock
[mysqld]
character-set-server = utf8
user = mysql
port = 8080
socket = /usr/local/mysql/mysql.sock
basedir = /usr/local/webserver/mysql
datadir = /usr/local/mysql/data
log-error = /usr/local/mysql/mysql_error.log
pid-file = /usr/local/mysql/mysql.pid
:wq 保存退出
启动mysql
/etc/init.d/mysqld start
附.2.1错误解决
./mysqld: Table 'mysql.plugin' doesn't exist
解决：
sudo find / -name mysql_install_db得到/usr/local/mysql/scripts/mysql_install_db
cd /usr/local/mysql/
执行/usr/local/mysql/scripts/mysql_install_db --user=mysql
附.3服务启动和停止
启动：/etc/init.d/mysqld start
停止：/etc/init.d/mysqld stop
或 ps -ef|grep mysql
Kill -9 XXX
重启：/etc/init.d/mysqld restart
附.4添加用户
/etc/init.d/mysqld stop
vi /etc/my.cnf
在[mysqld]下添加skip-grant-tables
/etc/init.d/mysqld start
ln -s /usr/local/mysql/bin/mysql /usr/bin
mysql/bin 目录
mysql -uroot -p
提示输入密码，直接回城
use mysql;
update user set password=password("TAIji123!") where user='root';
GRANT ALL PRIVILEGES ON . TO ‘root’@’%’ IDENTIFIED BY ‘root’;
flush privileges;
exit;
mysql开机启动
cp support-files/mysql.server /etc/rc.d/init.d/mysqld
1397
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
