mysql一主多从配置
查看主库的数据库
[root@localhost ~]# mysql -P3306 -h127.0.0.1 -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
查看从库的数据库
[root@localhost ~]# mysql -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
查看从库的数据库
[root@localhost ~]# mysql -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
把主库进行全备
锁定数据库
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.02 sec)
全备主数据库
[root@localhost ~]# mysqldump -P3306 -h127.0.0.1 --all-databases > all.sql
[root@localhost ~]# ls
all.sql
将主库的备份文件传送到C8 100 ，33从库
[root@localhost ~]# scp all.sql root@192.168.98.100:/opt/
The authenticity of host '192.168.98.100 (192.168.98.100)' can't be established.
ECDSA key fingerprint is SHA256:e+CTU5ttHC8Rx0HDSNfbXLUK8IZvipnVyCdGSEdVa1s.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.98.100' (ECDSA) to the list of known hosts.
root@192.168.98.100's password:
all.sql 100% 852KB 68.8MB/s 00:00
[root@localhost ~]# scp all.sql root@192.168.98.33:/opt/
The authenticity of host '192.168.98.33 (192.168.98.33)' can't be established.
ECDSA key fingerprint is SHA256:hnmkFkk7xO1t+qw9duFdJvxGY8W4Z4JcdKvbgK89IwE.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.98.33' (ECDSA) to the list of known hosts.
root@192.168.98.33's password:
all.sql 100% 852KB 47.7MB/s 00:00
在从库上面恢复主库的备份，保证主从库的数据一致
[root@localhost ~]# cd /opt
[root@localhost opt]# ls
all.sql data
[root@localhost opt]# cd
[root@localhost ~]# mysql < /opt/all.sql
[root@localhost ~]# mysql -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@localhost opt]# mysql < all.sql
[root@localhost opt]# mysql -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
在主数据库创建一个同步账号授权给数据库使用
mysql> create user 'repl'@'192.168.98.100' identified by 'repl123!';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to 'repl'@'192.168.98.100';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER 'repl'@'192.168.98.33' IDENTIFIED BY 'repl123!';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to 'repl'@'192.168.98.33';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
配置主数据库
[root@localhost ~]# vim /etc/my.cnf
log-bin=mysql_bin
server-id=10
重启mysql服务
[root@localhost ~]# pkill mysql
[root@localhost ~]# ps -aux | grep mysql
root 309898 0.0 0.0 12320 980 pts/3 S+ 07:28 0:00 grep --color=auto mysql
[root@localhost ~]# service mysqld_multi.server start
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 5 127.0.0.1:631 0.0.0.0:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 80 *:3307 *:*
LISTEN 0 80 *:3308 *:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 5 [::1]:631 [::]:*
查看主库的状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000002 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
配置从数据库
[root@localhost ~]# vim /etc/my.cnf
server-id=20
relay-log=mysql-relay-bin
重启从库的mysql服务
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 32 192.168.122.1:53 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 5 127.0.0.1:631 0.0.0.0:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 5 [::1]:631 [::]:*
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 32 192.168.122.1:53 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 5 127.0.0.1:631 0.0.0.0:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 5 [::1]:631 [::]:*
配置并启动主从复制
mysql> change master to
-> master_host='192.168.98.88',
-> master_user='repl',
-> master_password='repl123!',
-> master_log_file='mysql_bin.000002',
-> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
查看服务状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.98.88
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql_bin.000002
Slave_IO_Running: Yes //这里必须是yes
Slave_SQL_Running: Yes //这里必须是yes
第二太从库配置文件
[root@localhost ~]# vim /etc/my.cnf
server-id=2
relay-log=mysql_relay_bin
重启mysql服务
[root@localhost ~]# service mysqld start
Starting MySQL..... SUCCESS!
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 5 127.0.0.1:631 0.0.0.0:*
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 5 [::1]:631 [::]:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 [::]:111 [::]:*
查看从服务器状态
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.98.88
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: mysql_relay_bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql_bin.000002
Slave_IO_Running: Yes //必须是yes
Slave_SQL_Running: Yes //必须是yes
测试验证
在主库里面创建数据库
mysql> create database zdj;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zdj |
+--------------------+
5 rows in set (0.01 sec)
从库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zdj |
+--------------------+
5 rows in set (0.01 sec)
从库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zdj |
+--------------------+
5 rows in set (0.00 sec)
GTID的配置
在主库里面装创建一个同步账号并授权给从库使用
mysql> CREATE USER 'repl'@'192.168.98.33' IDENTIFIED BY 'repl123';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.98.33';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
在主数据库进行锁库操作，为了确保数据一致
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.01 sec)
主库进行全备
[root@localhost ~]# mysqldump --all-databases > all.sql
[root@localhost ~]# ls
all.sql
讲主库全备的数据传给从库33
[root@localhost ~]# scp all.sql root@192.168.98.33:/opt/
The authenticity of host '192.168.98.33 (192.168.98.33)' can't be established.
ECDSA key fingerprint is SHA256:hnmkFkk7xO1t+qw9duFdJvxGY8W4Z4JcdKvbgK89IwE.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.98.33' (ECDSA) to the list of known hosts.
root@192.168.98.33's password:
all.sql 100% 852KB 49.2MB/s 00:00
在从库中恢复全库传送过来的数据
[root@localhost ~]# mysql < /opt/all.sql
配置主库
[root@localhost ~]# vim /etc/my.cnf
server_id=10
gtid_mode=on
enforce_gtid_consistency=on
log_bin=master-binlog
log-slave-updates=1
binlog_format=row
skip_slave_start=1
配置从库
[root@localhost ~]# vim /etc/my.cnf
gtid_mode=on
enforce_gtid_consistency=on
server_id=20
log-bin=mysql_bin
log-slave-updates=1
binlog_format=row
skip_slave_start=1
重启从库
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
配置主从复制
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.98.100',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='repl123',
-> MASTER_PORT=3306,
-> MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
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.98.100
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql_bin.000001
Slave_IO_Running: Yes //这里必须是yes
Slave_SQL_Running: Yes //这里必须是yes
测试
mysql> create database zdj; 主 创建
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zdj |
+--------------------+
5 rows in set (0.00 sec)
从
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zdj |
+--------------------+
5 rows in set (0.00 sec)
gtid的主从复制
1、当一个事务在主库端执
行并提交时，产生GTID，一同记录到binlog日志中。
2、binlog传输到slave,并存储到slave的relaylog后，读取这个GTID的这个值设置gtid_next变量，即告诉Slave，下一个要执行的GTID值。
3、sql线程从relay log中获取GTID，然后对比slave端的binlog是否有该GTID。
4、如果有记录，说明该GTID的事务已经执行，slave会忽略。
5、如果没有记录，slave就会执行该GTID事务，并记录该GTID到自身的binlog，在读取执行事务前会先检查其他session持有该GTID，确保不被重复执行。
6、在解析过程中会判断是否有主键，如果有就用二级索引，如果没有就用全部扫描
传统主从与GTID主从的区别
GTID是基于mysql生成的事务ID，由服务器ID和事务ID组成。这个ID在主库和从库上都是唯一的，这个特性可以让mysql的主从复制更加简单，一致性更可靠。
传统主从在进行主从复制时需要寻找到master_log_file和master _log_posision，而gtid主从在进行主从复制时是不用去寻找的它只需要知道端口号以及设置自动寻找就可以了。
传统主从会有延迟，可能会发生数据丢失，主从数据一致性不高。
gtid主从是连续的，不会中断，主从数据一致性高，不会丢失数据。
gtid搭建主从更简单，比传统主从更安全。
扫一扫
370
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
