mysql主从数据库
主从作用
实时灾备，用于故障切换
读写分离，提供查询服务
备份，避免影响业务
主从形式
一主一从
主主复制
一主多从（ 扩展系统读取的性能，因为读是在从库读取的 ）
多主一从（ 5.7开始支持 ）
联级复制
主从数据库复制原理
当MySQL数据库执行数据的增、删、改操作时，会将这些事件记录到二进制日志binlog中，这些日志会被发送到每台从服务器上。 在从服务器上有两个线程：I/O线程和SQL线程，I/O线程用于接收日志并将其转换为中继日志relaylog；SQL线程将中继日志的内容进行应用，来实现主从数据一致
主从复制配置
主从复制配置步骤：
确保从数据库与主数据库里的数据一样
在主数据库里创建一个同步账号授权给从数据库使用
配置主数据库
配置从数据库
mysql安装
将主服务器yum源远程拷贝之从服务器上
由于做了免密登录，远程拷贝时不需要输密码
[root@controller ~]# cd /etc/yum.repos.d/
[root@controller yum.repos.d]# ls
CentOS-Base.repo
[root@controller yum.repos.d]# scp CentOS-Base.repo 192.168.200.20:/etc/yum.repos.d/
CentOS-Base.repo 100% 1653 848.9KB/s 00:00
[root@controller yum.repos.d]#
[root@node1 yum.repos.d]# ls
CentOS-Base.repo
[root@node1 yum.repos.d]#
将主从两台服务器上防火墙和selinux关闭
[root@controller ~]# systemctl disable --now firewalld
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@controller ~]#
[root@controller ~]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
Active: inactive (dead)
Docs: man:firewalld(1)
[root@controller ~]#
[root@controller ~]# setenforce 0
[root@controller ~]#
[root@controller ~]# getenforce
Permissive
[root@controller ~]#
分别在主从两台服务器上安装mysql
[root@controller ~]# yum -y install mariadb*
...
tzdata-java-2021e-1.el8.noarch
unixODBC-2.3.7-1.el8.x86_64 zlib-devel-1.2.11-17.el8.x86_64
Complete!
[root@controller ~]#
[root@node1 ~]# yum -y install mariadb*
将mariadb设置为开机自启，进入mysql设置密码
[root@controller ~]# systemctl enable --now mariadb
Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.
[root@controller ~]#
[root@controller ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
...
MariaDB [(none)]>
MariaDB [(none)]> set password = password('redhat123');
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> quit;
Bye
[root@controller ~]#
[root@node1 ~]# systemctl enable --now mariadb
Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.
[root@node1 ~]#
[root@node1 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
...
MariaDB [(none)]>
MariaDB [(none)]> set password = password('mhy123');
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> quit
Bye
[root@node1 ~]#
mysql主从数据库配置
确保主从数据库里的数据相同
若主从数据库里的数据不同，可以将主数据库进行全备并还原到从数据库中
先查看主从数据库中有哪些数据库
[root@controller ~]# mysql -uroot -predhat123 -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| xinge |
+--------------------+
[root@controller ~]#
[root@node1 ~]# mysql -uroot -pmhy123 -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
[root@node1 ~]#
在全备前需要另开一个终端进入数据库给数据库加上读锁，避免在全备时其他人写入数据导致数据不一致
[root@controller ~]# mysql -uroot -predhat123
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
...
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]>
全备主数据库
[root@controller ~]# mysqldump -uroot -predhat123 --all-databases > all-20230907.sql
[root@controller ~]#
[root@controller ~]# ls
all-20230907.sql anaconda-ks.cfg
[root@controller ~]#
全备完后，在另一个终端直接退出数据库，就可以解除主数据库的锁表状态
将备份文件传送到从数据库
[root@controller ~]# scp all-20230907.sql 192.168.200.20:/root/
all-20230907.sql 100% 469KB 120.3MB/s 00:00
[root@controller ~]#
[root@node1 ~]# ls
all-20230907.sql anaconda-ks.cfg
[root@node1 ~]#
在从数据库上恢复主数据库的备份文件并查看从数据库的数据库，确保与主数据库一致
[root@node1 ~]# mysql -uroot -pmhy123 < all-20230907.sql
[root@node1 ~]#
[root@node1 ~]# mysql -uroot -pmhy123 -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| xinge |
+--------------------+
[root@node1 ~]#
[root@controller ~]# mysql -uroot -predhat123 -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| xinge |
+--------------------+
[root@controller ~]#
在主数据库里创建一个同步账号授权给从数据库使用
[root@controller ~]# mysql -uroot -predhat123
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 15
...
MariaDB [(none)]>
MariaDB [(none)]> grant replication slave on *.* to 'repl'@'192.168.200.20' identified by 'repl123!';
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]>
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> quit;
Bye
[root@controller ~]#
配置主数据库
在[mysqld] 字段下添加内容，重启mariadb服务
[root@controller ~]# ls /etc/my.cnf.d/
auth_gssapi.cnf client.cnf enable_encryption.preset galera.cnf mariadb-server.cnf mysql-clients.cnf oqgraph.cnf
[root@controller ~]#
[root@controller ~]# vi /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
...
server-id = 10
log-bin = mysql_bin
[root@controller ~]# systemctl restart mariadb
[root@controller ~]#
[root@controller ~]# ls /var/lib/mysql/
aria_log.00000001 ib_buffer_pool ib_logfile0 ibtmp1 mysql mysql_bin.index mysql_upgrade_info xinge
aria_log_control ibdata1 ib_logfile1 multi-master.info mysql_bin.000001 mysql.sock performance_schema
[root@controller ~]#
查看主数据库状态
[root@controller ~]# mysql -uroot -predhat123
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
...
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000001 | 328 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)
MariaDB [(none)]>
配置从数据库
在[mysqld] 字段下添加内容，重启mariadb服务
[root@node1 ~]# ls /etc/my.cnf.d/
auth_gssapi.cnf client.cnf enable_encryption.preset galera.cnf mariadb-server.cnf mysql-clients.cnf oqgraph.cnf
[root@node1 ~]#
[root@node1 ~]# vi /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
...
server-id = 20
relay-log = myrelay
[root@node1 ~]# systemctl restart mariadb
[root@node1 ~]#
server-id 数据库服务器唯一标识符，从数据库的server-id值必须比主数据库的大
配置并启动主从数据库复制
由于之前从数据库执行了全备，再次进入从数据库时需要使用主数据库密码
[root@node1 ~]# mysql -uroot -predhat123
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
...
MariaDB [(none)]>
MariaDB [(none)]> change master to
-> master_host='192.168.200.10',
-> master_user='repl',
-> master_password='repl123!',
-> master_log_file='mysql_bin.000001',
-> master_log_pos=328;
Query OK, 0 rows affected (0.004 sec)
MariaDB [(none)]>
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]>
查看从数据库状态
MariaDB [(none)]> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.200.10
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000001
Read_Master_Log_Pos: 461
Relay_Log_File: myrelay.000002
Relay_Log_Pos: 688
Relay_Master_Log_File: mysql_bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
...
1 row in set (0.000 sec)
MariaDB [(none)]>
Slave_IO_Running和Slave_SQL_Running这两处必须为yes
测试验证
在主服务器的school数据库的stdent1表中插入数据：
[root@controller ~]# mysql -uroot -predhat123
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
...
MariaDB [(none)]>
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| xinge |
+--------------------+
5 rows in set (0.000 sec)
MariaDB [(none)]> use school;
Database changed
MariaDB [school]>
MariaDB [school]> create table student1(id int not null primary key auto_increment,name varchar(100) not null,age tinyint(4) null); Query OK, 0 rows affected (0.005 sec)
MariaDB [school]>
MariaDB [school]> insert student1(name,age) values('tom',20),('jerry',23),('sean',28),('zhangshan',26);
Query OK, 4 rows affected (0.001 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [school]>
MariaDB [school]> select * from student1;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | sean | 28 |
| 4 | zhangshan | 26 |
+----+-----------+------+
4 rows in set (0.000 sec)
MariaDB [school]>
在从数据库中查看数据是否同步：
[root@node1 ~]# mysql -uroot -predhat123
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 16
...
MariaDB [(none)]>
MariaDB [(none)]> start slave;
Query OK, 0 rows affected, 1 warning (0.000 sec)
MariaDB [(none)]>
MariaDB [(none)]> select * from school.student1;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | sean | 28 |
| 4 | zhangshan | 26 |
+----+-----------+------+
4 rows in set (0.000 sec)
MariaDB [(none)]>
同步成功！
225
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
