当前位置：首页 > news >正文

MySQL/MariaDB数据库主从复制之基于二进制日志的方式

news 2025/7/5 12:11:05

版权声明：原创作品，请勿转载！

实验介绍：本文从安装软件起介绍简单的一主一从配置方式，一主多从的配置方式类似。本次共选用两台主机进行演示，操作系统为centos7.9，数据库软件为MariaDB

主机	IP	安装软件
db01	10.0.0.51/24	mariadb-server
slave01	10.0.0.52/24	mariadb-server

1.软件安装

[root@db01 yum.repos.d]# yum install -y mariadb-server
[root@slave01 yum.repos.d]# yum install -y mariadb-server

2.修改数据库配置

2.1 主库db01配置

2.1.1 启动数据库并初始化设置root密码

此处仅演示db01上面的操作

[root@db01 ~]# systemctl start mariadb
[root@db01 ~]# systemctl enable mariadb
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
[root@db01 ~]# mysql_secure_installationNOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDBSERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.Enter current password for root (enter for none): 
OK, successfully used password, moving on...Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..... Success!By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.Remove anonymous users? [Y/n]    #直接回车... Success!Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.Disallow root login remotely? [Y/n] n      #可以自定义设置，此处是询问是否允许root远程登录... skipping.By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.Remove test database and access to it? [Y/n]    #直接回车- Dropping test database...... Success!- Removing privileges on test database...... Success!Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.Reload privilege tables now? [Y/n]     #直接回车... Success!Cleaning up...All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.Thanks for using MariaDB!

2.1.2 修改my.cnf配置文件并重启数据库

[root@db01 my.cnf.d]# pwd
/etc/my.cnf.d
[root@db01 my.cnf.d]# ll
总用量 16
-rw-r--r-- 1 root root 295 5月   6 2020 client.cnf
-rw-r--r-- 1 root root 232 5月   6 2020 mysql-clients.cnf
-rw-r--r-- 1 root root 744 5月   6 2020 server.cnf
-rw-r--r-- 1 root root 744 7月   3 17:28 server.cnf.bak
[root@db01 my.cnf.d]# vim server.cnf
[root@db01 my.cnf.d]# cat server.cnf
#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
## this is read by the standalone daemon and embedded servers
[server]# this is only for the mysqld standalone daemon
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
log-bin=ycl
server-id=51[root@db01 my.cnf.d]# systemctl restart mariadb

2.1.3 主库创建备份账户并给予相应权限

MariaDB [(none)]> create user 'slave01'@'10.0.0.%' identified by '123';
Query OK, 0 rows affected (0.01 sec)MariaDB [(none)]> grant replication slave on *.* to 'slave01'@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> show master status;
+------------+----------+--------------+------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------+----------+--------------+------------------+
| ycl.000001 |      560 |              |                  |
+------------+----------+--------------+------------------+
1 row in set (0.00 sec)MariaDB [(none)]> unlock tables;
Query OK, 0 rows affected (0.00 sec)

2.2 从库slave01配置

2.2.1 启动数据库并初始化设置root密码

slave01和db01操作相同，请参照上面2.1.1进行操作即可。

2.2.2 修改数据库配置文件

[root@slave01 my.cnf.d]# vim server.cnf
[root@slave01 my.cnf.d]# cat server.cnf
#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
## this is read by the standalone daemon and embedded servers
[server]# this is only for the mysqld standalone daemon
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
server-id=52[root@slave01 my.cnf.d]# systemctl restart mariadb

2.2.3 从库指定主库的连接信息，并开启备份

MariaDB [(none)]> change master to-> master_host='10.0.0.51',-> master_user='slave01',-> master_password='123',-> master_log_file='ycl.000001',-> master_log_pos=560;
Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

2.2.4 查看是否同步成功

可以看到IO_Running和SQL_Running都是YES状态，说明主从复制配置成功

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 10.0.0.51Master_User: slave01Master_Port: 3306Connect_Retry: 60Master_Log_File: ycl.000001Read_Master_Log_Pos: 560Relay_Log_File: mariadb-relay-bin.000002Relay_Log_Pos: 523Relay_Master_Log_File: ycl.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 560Relay_Log_Space: 819Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 51
1 row in set (0.00 sec)

3.主从同步测试

3.1 测试前

[root@db01 ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.68-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| class1             |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)[root@slave01 my.cnf.d]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.68-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| class1             |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

3.2 测试同步后

db01主库

MariaDB [(none)]> create database test_db;
Query OK, 1 row affected (0.00 sec)MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| class1             |
| mysql              |
| performance_schema |
| test_db            |
+--------------------+
5 rows in set (0.00 sec)

slave01从库

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| class1             |
| mysql              |
| performance_schema |
| test_db            |
+--------------------+
5 rows in set (0.00 sec)

主数据库服务器中使用class1，向数据库中userinfo表中插入一条数据（1,tom），查看从库同步情况

MariaDB [(none)]> use class1
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
MariaDB [class1]> insert into userinfo values (1, 'tom');
Query OK, 1 row affected (0.00 sec)MariaDB [class1]> select * from userinfo;
+----+------+
| id | name |
+----+------+
|  1 | tom  |
+----+------+
1 row in set (0.00 sec)
MariaDB [class1]> Bye
[root@db01 ~]# MariaDB [class1]> select * from userinfo;
+----+------+
| id | name |
+----+------+
|  1 | tom  |
+----+------+
1 row in set (0.00 sec)MariaDB [class1]> Bye
[root@slave01 my.cnf.d]#

主从复制配置就演示到这里啦~

查看全文

http://www.lryc.cn/news/580075.html