1. MySQL的一主两从 模式配置!
1.1 环境说明:
数据库角色 | IP | 应用与系统版本 | 有无数据 |
主数据库 | 192.168.180.100 | centos7/redhat7 mysql-5.7 | 有数据 |
从数据库 | 192.168.180.129 | centos7/redhat7 mysql-5.7 | 无数据 |
从数据库 | 192.168.180.131 | centos7/redhat7 mysql-5.7 | 无数据 |
1.2 MySQL的安装! 按照上述环境的要求,安装MySQL-5.7版本! (我安装完成了,就仅仅提供安装命令,提供不了过程截图!)
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
2. 开始主从配置!
2.1 因为主数据库上有数据,所以我们要先备份数据库并还原到从数据库中,保证主从数据一样!
(查看主数据库里面存在的数据!!!! 192.168.180.100)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lzz |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> select * from lzz.lp;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | gj | 20 |
| 2 | zh | 25 |
| 3 | xie | 23 |
| 4 | yebai | 27 |
+----+-------+------+
4 rows in set (0.00 sec)
(查看从数据库里面存在的数据!!!! 192.168.180.129 )
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
(查看从数据库里面存在的数据!!!! 192.168.180.131)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
2.2 全备主数据库,再备份的同时 再次新开一个终端,给数据上锁,再备份完成之后退出解锁!
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.01 sec)
mysql>
[root@lzz ~]# mysqldump -uroot -p123liuZEzheng! --all-databases > all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.01 sec)
mysql> quit
Bye
[root@lzz ~]#
2.3 将主数据库的备份内容传到从数据库上!
[root@lzz ~]# scp /root/all.sql root@192.168.180.129:/opt
root@192.168.180.129's password:
all.sql 100% 853KB 30.5MB/s 00:00
[root@lzz ~]# scp /root/all.sql root@192.168.180.131:/opt
The authenticity of host '192.168.180.131 (192.168.180.131)' can't be established.
ECDSA key fingerprint is SHA256:MY9S8r/GzYnf3JOQlPTeWKTnR36jcDwfckYHvvBj92Q.
Are you sure you want to continue connecting (yes/no/[fingerprint])? y
Please type 'yes', 'no' or the fingerprint: yes
Warning: Permanently added '192.168.180.131' (ECDSA) to the list of known hosts.
root@192.168.180.131's password:
all.sql 100% 853KB 41.3MB/s 00:00
2.4 查看两个从数据库,查看备份过去的是否和主数据库一致!
[root@localhost opt]# ls
all.sql data
[root@localhost opt]# mysql -uroot -p123liuZEzheng! < /opt/all.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost opt]# mysql -uroot -p123liuZEzheng! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| lzz |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@localhost opt]# mysql -uroot -p123liuZEzheng! -e 'select * from lzz.lp;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | gj | 20 |
| 2 | zh | 25 |
| 3 | xie | 23 |
| 4 | yebai | 27 |
+----+-------+------+
[root@localhost opt]#
[root@cong2 local]# cd /opt
[root@cong2 opt]# ls
all.sql data
[root@cong2 opt]# mysql -uroot -p123liuZEzheng! < /opt/all.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@cong2 opt]# mysql -uroot -p123liuZEzheng! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| lzz |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@cong2 opt]# mysql -uroot -p123liuZEzheng! -e 'select * from lzz.lp;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | gj | 20 |
| 2 | zh | 25 |
| 3 | xie | 23 |
| 4 | yebai | 27 |
+----+-------+------+
2.5 在主数据库创建一个同步帐号授权给从数据库!
mysql> create user 'ljj'@'192.168.180.129' IDENTIFIED BY 'ljj123';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'ljj'@'192.168.180.129';
Query OK, 0 rows affected (0.00 sec)
mysql> create user 'syz'@'192.168.180.131' IDENTIFIED BY 'syz123';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'syz'@'192.168.180.131';
Query OK, 0 rows affected (0.00 sec)
mysql>
2.6 配置主数据库!
[root@lzz ~]# vim /etc/my.cnf
[root@lzz ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
log-bin=mysql-bin
server-id=1
symbolic-links=0
log-error=/var/log/mysqld.log
[root@lzz ~]# systemctl restart mysqld
[root@lzz ~]# 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 [::]:*
LISTEN 0 80 *:3306 *:*
[root@lzz ~]#
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
2.7 配置第一个从数据库!
[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
server-id=2
relay-log=mysql-relay-bin
symbolic-links=0
log-error=/var/log/mysqld.log
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
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 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> CHANGE MASTER TO
-> MASTER_HOST='192.168.180.100',
-> MASTER_USER='ljj',
-> MASTER_PASSWORD='ljj123',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.33 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.180.100
Master_User: ljj
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay-bin.000004
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes (以下两个都是 yes)
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
2.8 配置第二个从数据库!
[root@cong2 opt]# vim /etc/my.cnf
[root@cong2 opt]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
server-id=10
relay-log=mysql-relay-bin
symbolic-links=0
log-error=/var/log/mysqld.log
[root@cong2 opt]# systemctl restart mysqld
[root@cong2 opt]# 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.180.100',
-> MASTER_USER='syz',
-> MASTER_PASSWORD='szy123',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start SLAVE IO_THREAD;
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.180.100
Master_User: syz
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay-bin.000005
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
2.9 测试实验!!
mysql> select * from lp; (在主数据库里面添加东西!)
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | gj | 20 |
| 2 | zh | 25 |
| 3 | xie | 23 |
| 4 | yebai | 27 |
+----+-------+------+
4 rows in set (0.00 sec)
mysql> insert into lp(name,age) values('hu',30),('xi',24);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from lp;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | gj | 20 |
| 2 | zh | 25 |
| 3 | xie | 23 |
| 4 | yebai | 27 |
| 5 | hu | 30 |
| 6 | xi | 24 |
+----+-------+------+
6 rows in set (0.00 sec)
[root@localhost ~]# mysql -uroot -p123liuZEzheng! -e 'select * from lzz.lp;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | gj | 20 |
| 2 | zh | 25 | (第一个从数据库!!!! 数据相同!)
| 3 | xie | 23 |
| 4 | yebai | 27 |
| 5 | hu | 30 |
| 6 | xi | 24 |
+----+-------+------+
[root@localhost ~]#
[root@cong2 opt]# mysql -uroot -p123liuZEzheng! -e 'select * from lzz.lp;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | gj | 20 |
| 2 | zh | 25 |
| 3 | xie | 23 |
| 4 | yebai | 27 | (第二个从数据库!!!!! 数据也更新了!!)
| 5 | hu | 30 |
| 6 | xi | 24 |
+----+-------+------+
[root@cong2 opt]#
3. gtid的主从配置!
3.1 环境说明!
数据库角色 | IP | 应用与系统版本 | 有无数据 |
主数据库 | 192.168.180.100 | centos7/redhat7 mysql-5.7 | 有数据 |
从数据库 | 192.168.180.129 | centos7/redhat7 mysql-5.7 | 无数据 |
从数据库 | 192.168.180.131 | centos7/redhat7 mysql-5.7 | 无数据 |
3.2 查看数据库,保证数据的统一,若不统一,则全备主数据库,然后传到从数据库!
主!!!
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
从数据库1!!!!!
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
从数据库2!!!
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
3.3 创建远程连接 并且授权!
mysql> create user 'laolin'@'192.168.180.129' IDENTIFIED BY 'll123';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'ljj'@'192.168.180.129';
Query OK, 0 rows affected (0.00 sec)
mysql> create user 'laolin1'@'192.168.180.131' IDENTIFIED BY 'll123';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'syz'@'192.168.180.131';
Query OK, 0 rows affected (0.00 sec)
3.3 配置主数据库!!
[root@lzz ~]# vim /etc/my.cnf
[root@lzz ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
server-id=1
gtid_mode=on
enforce_gitd_consistency=o
log_bin=master-binlog
log-slave-updates=1
binlog_format=row
skip_slave_start=1
[root@lzz ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| master-binlog.000001 | 154 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
3.4 配置第一个从数据库!!
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/mysql_data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/mysql_data/mysql.pid
user = mysql
skip-name-resolve
gtid_mode=on
enforce_gtid_consistency=on
server_id=2
log-bin=slave-binlog
log-slave-updates=1
binlog_format=row
skip_slave_start=1
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
CHANGE MASTER TO
MASTER_HOST='192.168.180.100',
MASTER_USER='laolin',
MASTER_PASSWORD='ll123.',
MASTER_PORT=3306,
MASTER_AUTO_POSITION = 1;
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.180.100
Master_User: laolin
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-binlog.000001
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 375
Relay_Master_Log_File: master-binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
3.5 配置第二个从数据库!
[root@cong2 ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/mysql_data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/mysql_data/mysql.pid
user = mysql
skip-name-resolve
gtid_mode=on
enforce_gtid_consistency=on
server_id=10
log-bin=slave-binlog
log-slave-updates=1
binlog_format=row
skip_slave_start=1
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
CHANGE MASTER TO
MASTER_HOST='192.168.180.100',
MASTER_USER='laolin1',
MASTER_PASSWORD='ll123.',
MASTER_PORT=3306,
MASTER_AUTO_POSITION = 1;
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.180.100
Master_User: laolin
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-binlog.000001
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 375
Relay_Master_Log_File: master-binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
3.6 在主数据库上插入数据 查看从数据库是否更新!
主数据库!
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lzz |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> select * from lzz.lp;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | gj | 20 |
| 2 | zh | 25 |
| 3 | xie | 23 |
| 4 | yebai | 27 |
| 5 | hu | 30 |
| 6 | xi | 24 |
+----+-------+------+
6 rows in set (0.01 sec)
第一个从数据库!
mysql> select * from lzz.lp;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | gj | 20 |
| 2 | zh | 25 |
| 3 | xie | 23 |
| 4 | yebai | 27 |
| 5 | hu | 30 |
| 6 | xi | 24 |
+----+-------+------+
6 rows in set (0.01 sec)
第二个从数据库!
mysql> select * from lzz.lp;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | gj | 20 |
| 2 | zh | 25 |
| 3 | xie | 23 |
| 4 | yebai | 27 |
| 5 | hu | 30 |
| 6 | xi | 24 |
+----+-------+------+
6 rows in set (0.01 sec)
传统主从和gtid主从的区别
传统主从
传统主从复制主要是基于二进制日志文件位置的复制,因此主必须启动二进制日志记录并建立唯一的服务器ID,复制组中的每个服务器都必须配置唯一的服务器ID。如果您省略server-id(或者明确地将其设置为其默认值0),则主设备将拒绝来自从设备的任何连接。
gtid主从
MySQL 5.6 的新特性之一,全局事务标识符(GTID)是创建的唯一标识符,并与在源(主)服务器上提交的每个事务相关联。此标识符不但是唯一的,而且在给定复制设置中的所有服务器上都是唯一的。所有交易和所有GTID之间都有一对一的映射关系 。它由服务器ID以及事务ID组合而成。这个全局事务ID不仅仅在原始服务器上唯一,在所有存在主从关系 的mysql服务器上也是唯一的。正是因为这样一个特性使得mysql的主从复制变得更加简单,以及数据库一致性更可靠。一个GTID在一个服务器上只执行一次,避免重复执行导致数据混乱或者主从不一致