主从同步配置(mysql)
mysql主从同步介绍
角色分2种:
数据库服务 做主master库:被客户端存储数据访问的库
数据库服务 做从slave库 同步主库的数据到本机
mysql主从同步原理
主库开启binglog日志用于记录主库的sql语句,主库中的Binlog Dump的程序告诉从库主库有新的sql命令写入到binlog日志中了,从库的I/O线程把binlog日志的sql写入到本机的中继日志中,SQL线程负责执行中继日志的sql命令,中继日志记录的是和主库binglog日志里一样的sql命令。
Mysql主从同步作用 :实现数据的自动备份
配置mysql主从同步结构
准备2台数据库服务器 192.168.4.51(master) 192.168.4.52(slave)
关闭seliux,firewalld ,确保两台机器可以保证通信状态
一主一从同步的缺点在于容易发生单点故障
主库配置步骤
1.启用binlog日志
2用户授权
3 查看正在使用的binlog日志信息
从配置步骤:192.168.4.52
1.验证授权用户
2 指定server_id
3 指定主库信息
让从库不做主库的方法是把/var/lib/mysql下的四个文件删掉重起数据库则可以实现从脱离主
master.info
host52-relay-bin.000001 ----中继日志
host52-relay-bin.index ----中继日志索引文件
relay-log.info
当前从库临时不同步主库信息
myql >stop slave;
在从库修改主库的信息
myql >stop slave;
change master tom 选项=“值”,选项=“值”
myql >start slave;
mysql> show processlist; ---当前数据库服务器正在工作的程序列表。主库用于查看Binlog Dump程序
+----+----------+--------------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+----------+--------------------+------+-------------+------+---------------------------------------------------------------+------------------+
| 3 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 6 | repluser | 192.168.4.52:57566 | NULL | Binlog Dump | 8 | Master has sent all binlog to slave; waiting for more updates | NULL |
+----+----------+--------------------+------+-------------+------+---------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
举例展示:
主库配置详细过程:
[[email protected] ~]# vim /etc/my.cnf
[mysqld]
validate_password_policy=0
validate_password_length=6
server_id=51
log-bin=master51 -----开启binglog日志
binlog-format="mixed"
[[email protected] ~]# systemctl restart mysqld
[[email protected] ~]# ls /var/lib/mysql/master51.* ------开启binglog日志和binglog日志的索引文件
/var/lib/mysql/master51.000001 /var/lib/mysql/master51.index
[[email protected] ~]# mysql -uroot -p123456
mysql: [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.7.17-log MySQL Community Server (GPL)
mysql> grant replication slave on *.* to [email protected]"192.168.4.52" identified by ''mysql> grant replication slave
mysql> grant replication slave on *.* to [email protected]"192.168.4.52" identified by "123456"; ----为用户授权
Query OK, 0 rows affected, 1 warning (0.07 sec)
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master51.000001 | 452 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
从库配置详细过程:
[[email protected] ~]# vim /etc/my.cnf
[mysqld]
validate_password_policy=0
validate_password_length=6
server_id=52
[[email protected] ~]# mysql -h 192.168.4.51 -urepluser -p123456
mysql: [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 4
Server version: 5.7.17-log MySQL Community Server (GPL)
mysql> change master to
-> master_host="192.168.4.51",
-> master_user="repluser",
-> master_password="123456",
-> master_log_file="123456",
-> master_log_pos=452;
Query OK, 0 rows affected, 2 warnings (0.35 sec)
mysql> show slave status\G;
Slave_IO_State:
Master_Host: 192.168.4.51
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master51.000001
Read_Master_Log_Pos: 452
Relay_Log_File: host52-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master51.000001
Slave_IO_Running: No
Slave_SQL_Running: No
mysql> start slave;
Query OK, 0 rows affected (0.05 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.4.51
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master51.000001
Read_Master_Log_Pos: 452
Relay_Log_File: host52-relay-bin.000002
Relay_Log_Pos: 319
Relay_Master_Log_File: master51.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[[email protected] ~]# cd /var/lib/mysql
[[email protected] mysql]# ls
auto.cnf host52-relay-bin.000001 ib_logfile0 mysql.sock relay-log.info
ca-key.pem host52-relay-bin.000002 ib_logfile1 mysql.sock.lock server-cert.pem
ca.pem host52-relay-bin.index ibtmp1 performance_schema server-key.pem
client-cert.pem ib_buffer_pool master.info private_key.pem sys
client-key.pem ibdata1 mysql public_key.pem
[[email protected] mysql]# cat master.info ----主机的信息,启动服务的时候会自动的加载改该文件获得主库的信息
25
master51.000001
452
192.168.4.51
repluser
123456
3306
60
0
0
30.000
0
422d96a2-8dab-11e8-ab41-525400e5b012
86400
0
[[email protected] mysql]# cat relay-log.info -----中继日志
7
./host52-relay-bin.000002 ---当前目录下的中继日志名
319 -----当前目录下的中继的偏移量
master51.000001 -----主机的binlog信息
452 ------主机的偏移量
0
0
1
host52-relay-bin.000001 ----中继日志
host52-relay-bin.index ----中继日志索引文件
验证
验证过程:在主数据库51上对一个用户授权让其有有对某一个数据库有所有权限,在用客户端用该用户的权限进行登录,并创建出主库授权给
客户端的库,在库上进行相应的操作,在主库的binlog日志文件中则会记录下,客户端的操作的sql日志文件。
[[email protected] ~]# mysql -uroot -p123456
mysql: [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.7.17-log MySQL Community Server (GPL)
mysql> grant all on zhuhaiyan.* to [email protected]"%" identitied by "123456";
[[email protected] ~]# mysql -h192.168.4.51 -uzhu -p123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.17-log MySQL Community Server (GPL)
MySQL [(none)]> create database zhuhaiyan;
Query OK, 1 row affected (0.04 sec)
[[email protected] ~]# cd /var/lib/mysql
[[email protected] mysql]# ls
auto.cnf client-key.pem ib_logfile1 mysql private_key.pem sys
ca-key.pem ib_buffer_pool ibtmp1 mysql.sock public_key.pem zhuhaiyan
ca.pem ibdata1 master51.000001 mysql.sock.lock server-cert.pem
client-cert.pem ib_logfile0 master51.index performance_schema server-key.pem
[[email protected] mysql]# mysqlbinlog master51.000001 mysql]# mysqlbinlog master51.000001 |grep "create"
#180722 21:37:37 server id 51 end_log_pos 123 CRC32 0xfb2770e6 Start: binlog v 4, server v 5.7.17-log created 180722 21:37:37 at startup
create database zhuhaiyan
[[email protected] mysql]# cd /var/lib/mysql
[[email protected] mysql]# mysqlbinlog host52-relay-bin.000002 | grep "create"
#180722 22:21:31 server id 52 end_log_pos 123 CRC32 0x0bf2a5e8 Start: binlog v 4, server v 5.7.17 created 180722 22:21:31
#180722 21:37:37 server id 51 end_log_pos 0 CRC32 0xc61daaee Start: binlog v 4, server v 5.7.17-log created 180722 21:37:37
create database zhuhaiyan