mysql数据库主从复制
文章目录
1:简介
数据库运行时，一些因素可能会导致服务运行不正常，用户访问数据受阻。对于互联网公司，尤其是购物网站而言，这种情况造成的损失是无法估量的。因此，对数据库进行“备份”也是必不可少的操作。当主要的数据库死机时，系统能够快速地切换到备用的数据库上。本章将详细介绍数据库集群中的主从复制原理和操作流程。
2：主从形式
一主一从
主主复制
一主多从—扩展系统读取的性能，因为读是在从库读取的
多主一从—5.7开始支持
联级复制
3：主从复制原理
主从复制又被称为AB复制，主要用于实现数据库集群中的数据同步。实现MySQL的AB复制时，数据库的版本应尽量保持一致。
在主从复制集群中，主数据库把数据更改的操作记录到二进制日志中，从数据库分别启动I/O线程和SQL线程，用于将主数据库中的数据复制到从数据库中。其中，I/O线程主要将主数据库上的日志复制到自己的中继日志中，SQL线程主要用于读取中继日志中的事件，并将其重放到从数据库之上。另外，系统会将I/O线程已经读取的二进制日志的位置信息存储在master.info文件中，将SQL线程已经读取的中继日志的位置信息存储在relay-log.info文件中。随着版本的更新，在MySQL 5.6.2之后，MySQL允许将这些状态信息保存在Table中，不过在更新之前需要用户在配置文件中进行声明，具体的参数如下。
4：主从复制配置
步骤
1·确保从数据库与主数据库里的数据一样
2·在主数据库里创建一个同步账号授权给从数据库使用
3·配置主数据库（修改配置文件）
4·配置从数据库（修改配置文件）
环境说明
| 数据库角色 | ip | 应用与系统版本 | 有无数据 |
|---|---|---|---|
| 主数据库 | 192.168.226.139 | centos8 | 有 |
| 从数据库 | 192.168.226.158 | redhat8 | 无 |
在实验开始之前要确保两台服务器上有mysql服务
4.1 确保主从数据库里的数据都要一样
查看有哪些库哪些表
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| nian |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
mysql>
mysql> use nian;
Database changed
mysql> show tables;
+----------------+
| Tables_in_nian |
+----------------+
| xuan |
+----------------+
1 row in set (0.00 sec)
mysql>
全备主数据库，并给数据库上锁
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
[root@master ~]# mysqldump -uroot -p123456789 --all-databases > all-2022-07-29.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@master ~]# ls
公共 模板 视频 图片 文档 下载 音乐 桌面 all-2022-07-29.sql anaconda-ks.cfg initial-setup-ks.cfg
[root@master ~]# scp all-2022-07-29.sql root@192.168.226.158:/
The authenticity of host '192.168.226.158 (192.168.226.158)' can't be established.
ECDSA key fingerprint is SHA256:ruZxylllox/yy17nVIQPDXNcQoydqXbjt6rjkhTQgyE.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.226.158' (ECDSA) to the list of known hosts.
root@192.168.226.158's password:
all-2022-07-29.sql 100% 856KB 65.8MB/s 00:00
[root@master ~]#
[root@clave /]# ls
all-2022-07-29.sql bin boot dev etc home lib lib64 media mnt opt proc root run sbin srv sys tmp usr var
解除主库的锁表状态，直接退出即可
mysql> exit
Bye
在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致
[root@slave /]# mysql -uroot -p123456789
mysql> source all-2022-07-29.sql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| nian |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use nian;
Database changed
mysql> show tables;
+----------------+
| Tables_in_nian |
+----------------+
| xuan |
+----------------+
1 row in set (0.00 sec)
4.2在主数据库里创建一个同步账号授权给从数据库使用
mysql> create user 'xuanning'@'192.168.226.158' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'xuanning'@'192.168.226.158';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
4.3 配置主数据库配置文件
[root@master local]# vim /etc/my.cnf
log-bin=g
server-id=2000
[root@master data]# 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 |
+----------+----------+--------------+------------------+-------------------+
| g.000002 | 154 | | | |
+----------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
4.4配置从数据库配置文件
[root@slave /]# vim /etc/my.cnf
log-bin=j
server-id=2001
[root@slave /]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.Logging to '/opt/data/slave.err'.
SUCCESS!
mysql> change master to
-> master_host='192.168.226.139' ,
-> master_user = 'xuanning' ,
-> master_password = '123456' ,
-> master_log_file = 'g.000002' ,
-> master_log_pos = 154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.226.139
Master_User: xuanning
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: g.000003
Read_Master_Log_Pos: 154
Relay_Log_File: slave-relay-bin.000007
Relay_Log_Pos: 351
Relay_Master_Log_File: g.000003
Slave_IO_Running: Yes <<这里一定要是yes
Slave_SQL_Running: Yes
4.5 关闭主从服务器的防火墙和selinux
[root@slave /]# systemctl stop firewalld.service
[root@slave /]# vim /etc/selinux/config
SELINUX=disable
[root@slave /]# setenforce 0
4.6 测试
查看主从服务器表的内容
mysql> use nian;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from xuan;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | xuanning | 21 |
| 2 | nianxia | 22 |
| 3 | zhangsan | 23 |
| 4 | lisi | 24 |
+----+----------+------+
4 rows in set (0.00 sec)
mysql> use nian;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from xuan;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | xuanning | 21 |
| 2 | nianxia | 22 |
| 3 | zhangsan | 23 |
| 4 | lisi | 24 |
+----+----------+------+
4 rows in set (0.00 sec)
在主服务器中插入数据
mysql> insert xuan(id,name,age) values (5,'zhaosi',30);
Query OK, 1 row affected (0.01 sec)
mysql> select * from xuan;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | xuanning | 21 |
| 2 | nianxia | 22 |
| 3 | zhangsan | 23 |
| 4 | lisi | 24 |
| 5 | zhaosi | 30 |
+----+----------+------+
5 rows in set (0.00 sec)
在从数据库中查看是否同步过来
mysql> select * from xuan;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | xuanning | 21 |
| 2 | nianxia | 22 |
| 3 | zhangsan | 23 |
| 4 | lisi | 24 |
| 5 | zhaosi | 30 |
+----+----------+------+
5 rows in set (0.00 sec)
5:mysql多实例做主从复制
在主数据库中创建远程连接用户用作同步
mysql> create user 'xuanning'@'192.168.226.158' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'xuanning'@'192.168.226.158';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
关闭防火墙和selinux
[root@slave /]# systemctl stop firewalld.service
[root@slave /]# vim /etc/selinux/config
SELINUX=disable
[root@slave /]# setenforce 0
配置本地文件开启二进制日志并设置服务id主库id一定要比从库小
[mysqld3306]
datadir = /opt/data/3306
port = 3306
socket = /tmp/mysql3306.sock
pid-file = /opt/data/3306/mysql_3306.pid
log-error=/var/log/3306.log
log-bin=g
server-id=2000
[mysqld3307]
datadir = /opt/data/3307
port = 3307
socket = /tmp/mysql3307.sock
pid-file = /opt/data/3307/mysql_3307.pid
log-error=/var/log/3307.log
log-bin=o
server-id=2001
进入数据库连接
mysql> change master to
-> master_host='192.168.226.139' ,
-> master_user='xuanning' ,
-> master_password='123456' ,
-> master_log_file='g.000001' ,
-> master_log_pos=154 ,
-> master_port=3306; //端口
Query OK, 0 rows affected, 2 warnings (0.02 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.226.139
Master_User: xuanning
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: g.000001
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 312
Relay_Master_Log_File: g.000001
Slave_IO_Running: Yes //此处一定要是yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
查看主数据库库与从数据库里面的数据
[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql3306.sock
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.37-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> exit
Bye
[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql3307.sock
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 5
Server version: 5.7.37-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql>
测试
[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql3306.sock
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.37-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database nian;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| nian |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> exit
Bye
[root@localhost ~]# mysql -uroot -p123456 -S /tmp/mysql3307.sock
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 6
Server version: 5.7.37-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| nian |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql>
6:mysql配置systemctl管理
写入配置文件,关闭mysql服务
# vim /usr/lib/systemd/system/mysql.service
[Unit]
Description= mysql server daemon
After=network.target
[Service]
Type=forking
ExecStart=/usr/local/mysql/bin/mysqld_multi start mysql
ExecStop=ps -ef | grep mysql | grep -v grep | awk '{print$2}' | xargs kill -9
ExecReload=/bin/kill -HUP $MAINPID
[Install]
WantedBy=muyi-user.target
[root@localhost local]# systemctl stop mysqld.service
[root@localhost local]# 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 127.0.0.1:6010 0.0.0.0:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 5 [::1]:631 [::]:*
LISTEN 0 128 [::1]:6010 [::]:*