前言:MySQL的复制功能,使得数据可以从一台MySQL 数据库服务器(我们称之为主库,即 master),复制到另外一台或者多台MySQL 数据库服务器(我们称之为从库,即 slave)。在默认情况下,复制的过程是异步的,因此,从数据库服务器不需要一直连接到主数据库服务器接收更新。 这也意味着,更新可以在长距离连接,甚至在诸如拨号服务临时或者间歇性的情况下继续。MySQL的复制功能,可以复制所有数据库,或者需要复制的几个数据库,甚至数据库中需要复制的数据库表,这都依赖于你是如何配置的。
1、
在master上的操作
1.修改my.cnf的配置文件
vim /etc/my.cnf
一般情况修改原始配置之前建议先备份文件
cp /etc/my.cnf /etc/my.cnf.bak
修改说明如下,在[mysqld]这块进行增加
#Server ID,一般设置成IP地址的最后一位
server_id=52
#开启log bin,名字最好有意义用来区分
log-bin=dev-bin
#需要进行复制的数据库,可以指定数据库,这里我注释掉不用
#binlog-do-db=DB_master
#不需要备份的数据库,可以设置多个数据库,一般不会同步mysql这个库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
#为每个session 分配的内存,在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1m
#二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。
expire_logs_days=7
# 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。
# 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
server-id=1 #服务器id (主从必须不一样),爱写啥写啥,别太离谱就行
log-bin=mysql-bin #打开日志(主机需要打开),这个mysql-bin也可以自定义,这里也可以加上路径
#作为主机的配置
binlog-do-db=master #要给从机同步的库
binlog-ignore-db=mysql #不给从机同步的库(多个写多行)
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
expire_logs_days=7 #自动清理 7 天前的log文件,可根据需要修改
重启mysql
systemctl restart mysqld
测试log_bin是否成功开启
登录mysql 执行
show variables like '%log_bin%';
2.创建数据库并且进行测试操作
进行登陆mysql
mysql -uroot -p
查看mysql 的 master 的状态
show master status ;
创建测试数据库,并且插入一些数据
create database DB_master ;
use DB_master
创建表
CREATE TABLE `edu_user` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`userName` varchar(255) NOT NULL DEFAULT '' COMMENT '用户名',
`pwd` varchar(255) NOT NULL DEFAULT '' COMMENT '密码',
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='用户信息表';
插入一些数据
INSERT INTO `edu_user` VALUES (1,'吴水成','123456'),(2,'清风','123456'),(3,'龙果','roncoo.com');
3.为了主从数据库一致,将进行先锁表,并且导出数据
FLUSH TABLES WITH READ LOCK
1.FLUSH TABLES WITH READ LOCK
这个命令是全局读锁定,执行了命令之后所有库所有表都被锁定只读。一般都是用在数据库联机备份,这个时候数据库的写操作将被阻塞,读操作顺利进行。
解锁的语句是unlock tables。
锁表后进行测试,成功锁表,将进行备份
退出数据库并且进行备份
mysqldump -u root -p --databases DB_master > DB_masterSlave.sql
登陆数据库查看Pos值并且解锁数据表
show master status ;
unlock tables;
show master status\G #
这个图你要有记录,一会设置从库的时候需要里面的参数。
将备份数据库scp到从数据库的主机上面
scp DB_masterSlave.sql allenwu@192.168.23.53:/tmp
登陆数据库,创建备份使用的用户,并且赋予相应的权限
grant replication slave, replication client on *.* to 'repl''192.168.23.53' identified by 'slavePassword!123';
flush privileges ;
成功后查看账号是否存在
use mysql
select user,authentication_string,host from user;
三、Slave的配置
1.修改 /etc/my.cnf 配置文件
在[mysqld]这个模块进行添加参数
server_id=9
#binlog-ignore-db=mydql
#binlog-ignore-db=information_schema
#binlog-ignore-db=performance_schema
#log-bin=dev-slave-bin
binlog_cache_size=1M
binlog_format=mixed
expire_logs_days=7
slave_skip_errors=1062
relay_log=dev-relay-bin
#log_slave_updates=1
read_only=1
保存并且重启数据库
systemctl restart mysqld
登陆数据库,并且还原master的数据到slave
source /tmp/DB_masterSlave.sql
从数据库连接到主数据库
change master to master_host='192.168.23.52',master_user='repl',master_password='slavePassword',master_port=3306,master_log_file='dev-bin.000001',master_log_pos=1435,master_connect_retry=30;
查看slave状态
show slave status \G
启动slave后并且再查看状态
start slave;
show slave status \G
显示为已经连接上,主从搭建已经完成,
登录从库的mysql 然后执行,下面的参数自己去找主库我刚刚然你记录下来的数值填写。
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to
-> master_host='10.8.18.73',
-> master_user='backup',
-> master_password='123456',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=610;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
查看slave从机的状态
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 10.8.18.73
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 610
Relay_Log_File: BJ-FT-VM-136-48-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Slave_IO_Running: Connecting 显示的是连接中,说明没有连上主库,可能是主库 3306没有开放,也可能是防火墙或网络等问题。
后面为检验
在master在DB_master的edu_user表里面添加一些数据,再到slave上面查看是否已经同步
先查看slave现在数据
use DB_master
select * from edu_user ;
在master里面添加数据
INSERT INTO `edu_user` VALUES (4,'吴水成','123456'),(5,'清风','123456'),(6,'龙果','roncoo.com');
分别在master和slave查看数据
master:
slave:
show processlist\G;
同步成功,后面就是优化过程