Mariadb数据库主从复制同步配置过程实例

目录

• 一、环境规划
• 二、Mariadb的主从复制介绍
• 1.主从复制简介
• 2.半同步复制介绍
• 3.主从复制原理图
• 三、安装Mariadb
• 1.配置yum仓库
• 2.检查yum仓库
• 3.安装mariadb
• 4.启动mariadb服务
• 5.从节点安装mariadb
• 四、mariadb主库配置
• 1.mariadb的初始化
• 2.修改主库配置文件
• 3.重启mariadb服务
• 五、mariadb从库配置
• 1.修改从库node01节点的server.cnf文件
• 2.修改从库node02节点的server.cnf文件
• 3.重启node01和node02的mariadb服务
• 六、查看mariadb主库状态
• 1.创建数据库用户
• 2.查看数据库用户信息
• 3.查看主库状态
• 4.查看mysql-bin日志文件
• 5.gtid查询
• 七、启动从库
• 1.从库设置主库的gtid
• 2.连接主库
• 3.启动从库
• 4.查询从库状态
• 八、测试主从同步
• 1.主库写入数据
• 2.主库查看数据表
• 3.从库查看数据表

一、环境规划

hostnameIP地址系统版本角色master192.168.3.171centos 7.6主节点node01192.168.3.172centos 7.6从节点node02192.168.3.173centos 7.6从节点

二、Mariadb的主从复制介绍

1.主从复制简介

主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。

2.半同步复制介绍

半同步复制是解决主库数据挂掉，从库数据不一致的问题。 解决方法：半同步复制，半同步复制场景中，master会监视所有的slave，确保其中至少一台数据完全同步成功，master才会返回客户端信息，此数据写成功。

3.主从复制原理图

三、安装Mariadb

1.配置yum仓库

3个节点都安装Mariadb数据库

[root@master yum.repos.d]# cat mariadb.repo # MariaDB 10.6 CentOS repository list - created 2021-12-27 11:21 UTC# https://mariadb.org/download/[mariadb]name = MariaDBbaseurl = https://mirrors.ustc.edu.cn/mariadb/yum/10.6/centos7-amd64gpgkey=https://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDBgpgcheck=0

 sed -i "s#//mirrors.xtom.com.hk#//mirrors.ustc.edu.cn#g" /etc/yum.repos.d/mariadb.repo

2.检查yum仓库

[root@master yum.repos.d]# yum repolist allLoaded plugins: fastestmirrorLoading mirror speeds from cached hostfilerepo id  repo name statusmariadb  MariaDB   enabled: 96repolist: 96

3.安装mariadb

yum -y install mariadb-server

4.启动mariadb服务

[root@master yum.repos.d]# systemctl start mariadb[root@master yum.repos.d]# systemctl enable  mariadbCreated symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.[root@master yum.repos.d]# 

5.从节点安装mariadb

如上步骤安装即可。

四、mariadb主库配置

1.mariadb的初始化

[root@master ~]# mariadb-secure-installation NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!In order to log into MariaDB to secure it, we"ll need the currentpassword for the root user. If you"ve just installed MariaDB, andhaven"t set the root password yet, you should just press enter here.Enter current password for root (enter for none): OK, successfully used password, moving on...Setting the root password or using the unix_socket ensures that nobodycan log into the MariaDB root user without the proper authorisation.You already have your root account protected, so you can safely answer "n".Switch to unix_socket authentication [Y/n] n ... skipping.You already have your root account protected, so you can safely answer "n".Change the root password? [Y/n] n ... skipping.By default, a MariaDB installation has an anonymous user, allowing anyoneto log into MariaDB without having to have a user account created forthem.  This is intended only for testing, and to make the installationgo a bit smoother.  You should remove them before moving into aproduction environment.Remove anonymous users? [Y/n] y ... Success!Normally, root should only be allowed to connect from "localhost".  Thisensures that someone cannot guess at the root password from the network.Disallow root login remotely? [Y/n] n ... skipping.By default, MariaDB comes with a database named "test" that anyone canaccess.  This is also intended only for testing, and should be removedbefore moving into a production environment.Remove test database and access to it? [Y/n] y - Dropping test database... ... Success! - Removing privileges on test database... ... Success!Reloading the privilege tables will ensure that all changes made so farwill take effect immediately.Reload privilege tables now? [Y/n] y ... Success!Cleaning up...All done!  If you"ve completed all of the above steps, your MariaDBinstallation should now be secure.Thanks for using MariaDB![root@master ~]# 

2.修改主库配置文件

[root@master ~]# grep -Ev "^$|^#" /etc/my.cnf.d/server.cnf[server][mysqld]character-set-server=utf8collation-server=utf8_general_ciserver_id = 12    #  一组主从组里的每个id必须是唯一值。推荐用ip位数log-bin= mysql-bin # 二进制日志，后面指定存放位置。如果只是指定名字，默认存放在/var/lib/mysql下lower_case_table_names=1 # 不区分大小写binlog-format=ROW    # 二进制日志文件格式log-slave-updates=True    # slave更新是否记入日志sync-master-info=1    # 值为1确保信息不会丢失slave-parallel-threads=3 #同时启动多少个复制线程，最多与要复制的数据库数量相等即可binlog-checksum=CRC32    # 效验码master-verify-checksum=1    # 启动主服务器效验slave-sql-verify-checksum=1   # 启动从服务器效验[galera][embedded][mariadb][mariadb-10.6][root@master ~]# 

3.重启mariadb服务

[root@master ~]# systemctl restart mariadb[root@master ~]# 

五、mariadb从库配置

1.修改从库node01节点的server.cnf文件

[root@node01 ~]# cat  /etc/my.cnf.d/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]character-set-server=utf8collation-server=utf8_general_ciserver_id=15#log-bin= mysql-bin #log-bin是二进制文件relay_log = relay-bin    # 中继日志, 后面指定存放位置。如果只是指定名字，默认存放在/var/lib/mysql下lower_case_table_names=1## * Galera-related settings#[galera]# Mandatory settings#wsrep_on=ON#wsrep_provider=#wsrep_cluster_address=#binlog_format=row#default_storage_engine=InnoDB#innodb_autoinc_lock_mode=2## Allow server to accept connections on all interfaces.##bind-address=0.0.0.0## Optional setting#wsrep_slave_threads=1#innodb_flush_log_at_trx_commit=0# this is only for embedded server[embedded]# This group is only read by MariaDB servers, not by MySQL.# If you use the same .cnf file for MySQL and MariaDB,# you can put MariaDB-only options here[mariadb]# This group is only read by MariaDB-10.6 servers.# If you use the same .cnf file for MariaDB of different versions,# use this group for options that older servers don"t understand[mariadb-10.6]

2.修改从库node02节点的server.cnf文件

[root@node02 ~]# cat  /etc/my.cnf.d/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]character-set-server=utf8collation-server=utf8_general_ciserver_id=16#log-bin= mysql-bin #log-bin是二进制文件relay_log = relay-bin    # 中继日志, 后面指定存放位置。如果只是指定名字，默认存放在/var/lib/mysql下lower_case_table_names=1## * Galera-related settings#[galera]# Mandatory settings#wsrep_on=ON#wsrep_provider=#wsrep_cluster_address=#binlog_format=row#default_storage_engine=InnoDB#innodb_autoinc_lock_mode=2## Allow server to accept connections on all interfaces.##bind-address=0.0.0.0## Optional setting#wsrep_slave_threads=1#innodb_flush_log_at_trx_commit=0# this is only for embedded server[embedded]# This group is only read by MariaDB servers, not by MySQL.# If you use the same .cnf file for MySQL and MariaDB,# you can put MariaDB-only options here[mariadb]# This group is only read by MariaDB-10.6 servers.# If you use the same .cnf file for MariaDB of different versions,# use this group for options that older servers don"t understand[mariadb-10.6]

3.重启node01和node02的mariadb服务

systemctl restart mariadb

六、查看mariadb主库状态

1.创建数据库用户

MariaDB [(none)]>  grant replication slave, replication client on *.* to "redhat"@"%"  identified by "admin";Query OK, 0 rows affected (0.025 sec)MariaDB [(none)]> flush privileges;Query OK, 0 rows affected (0.000 sec)

2.查看数据库用户信息

MariaDB [(none)]> select user,password,host from mysql.user;+-------------+-------------------------------------------+-----------+| User| Password  | Host      |+-------------+-------------------------------------------+-----------+| mariadb.sys |   | localhost || root| invalid   | localhost || mysql       | invalid   | localhost || redhat      | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 | % |+-------------+-------------------------------------------+-----------+4 rows in set (0.002 sec)MariaDB [(none)]> 

3.查看主库状态

MariaDB [(none)]> show master status;+------------------+----------+--------------+------------------+| File     | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000002 |      659 |      |  |+------------------+----------+--------------+------------------+1 row in set (0.000 sec)MariaDB [(none)]> 

4.查看mysql-bin日志文件

[root@master ~]#  ll /var/lib/mysql/mysql-bin.index -rw-rw----. 1 mysql mysql 38 Nov 23 23:31 /var/lib/mysql/mysql-bin.index[root@master ~]# cat  /var/lib/mysql/mysql-bin.index ./mysql-bin.000001./mysql-bin.000002

5.gtid查询

MariaDB [(none)]> select binlog_gtid_pos("mysql-bin.000002",659);+-----------------------------------------+| binlog_gtid_pos("mysql-bin.000002",659) |+-----------------------------------------+| 0-12-2  |+-----------------------------------------+1 row in set (0.000 sec)MariaDB [(none)]> 

七、启动从库

1.从库设置主库的gtid

MariaDB [(none)]>  set global gtid_slave_pos="0-12-2";Query OK, 0 rows affected (0.080 sec)

2.连接主库

MariaDB [(none)]>  change master to master_host="192.168.3.171",master_user="redhat",master_password="admin",master_use_gtid=slave_pos;Query OK, 0 rows affected (0.182 sec)

3.启动从库

MariaDB [(none)]>  start slave;Query OK, 0 rows affected (0.123 sec)

4.查询从库状态

MariaDB [(none)]> show  slave status\G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send event   Master_Host: 192.168.3.171   Master_User: redhat   Master_Port: 3306 Connect_Retry: 60       Master_Log_File: mysql-bin.000002   Read_Master_Log_Pos: 1450Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 1461 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:        Replicate_Wild_Do_Table:    Replicate_Wild_Ignore_Table:     Last_Errno: 0    Last_Error:   Skip_Counter: 0   Exec_Master_Log_Pos: 1450       Relay_Log_Space: 1764       Until_Condition: NoneUntil_Log_File:  Until_Log_Pos: 0    Master_SSL_Allowed: No    Master_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: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error:    Replicate_Ignore_Server_Ids:       Master_Server_Id: 12Master_SSL_Crl:     Master_SSL_Crlpath:     Using_Gtid: Slave_Pos   Gtid_IO_Pos: 0-12-7       Replicate_Do_Domain_Ids:    Replicate_Ignore_Domain_Ids:  Parallel_Mode: optimistic     SQL_Delay: 0   SQL_Remaining_Delay: NULL       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates      Slave_DDL_Groups: 3Slave_Non_Transactional_Groups: 2    Slave_Transactional_Groups: 01 row in set (0.000 sec)MariaDB [(none)]> 

八、测试主从同步

1.主库写入数据

MariaDB [(none)]> create database school;Query OK, 1 row affected (0.001 sec)MariaDB [(none)]> use school;Database changedMariaDB [school]> CREATE TABLE IF NOT EXISTS `student`(    ->    `id` INT UNSIGNED AUTO_INCREMENT,    ->    `name` VARCHAR(100) NOT NULL,    ->    `gender` TINYINT NOT NULL,    ->    `age` INT UNSIGNED,    ->    `class` INT UNSIGNED,    ->      `course` VARCHAR(100) NOT NULL,    ->     `grade` INT UNSIGNED,    ->    PRIMARY KEY ( `id` )    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;Query OK, 0 rows affected (0.206 sec)MariaDB [school]> insert into student ( name, gender, age, class, Course, grade ) values ( "高峰", "0", "22", "4", "英语", "100"), ( "陈林", "1", "15", "5", "化学", "99" );Query OK, 2 rows affected (0.012 sec)Records: 2  Duplicates: 0  Warnings: 0MariaDB [school]> insert into student ( name, gender, age, class, course, grade ) values  ( "王明", "0", "16", "2", "数学", "88"),  ( "万易", "0", "17", "4", "地理", "79" ), ( "李依依", "1", "17", "3", "语文",  "90"  ) ;Query OK, 3 rows affected (0.017 sec)Records: 3  Duplicates: 0  Warnings: 0

2.主库查看数据表

MariaDB [school]> select * from school.student;+----+-----------+--------+------+-------+--------+-------+| id | name      | gender | age  | class | course | grade |+----+-----------+--------+------+-------+--------+-------+|  1 | 高峰      |      0 |   22 |     4 | 英语   |   100 ||  2 | 陈林      |      1 |   15 |     5 | 化学   |    99 ||  3 | 王明      |      0 |   16 |     2 | 数学   |    88 ||  4 | 万易      |      0 |   17 |     4 | 地理   |    79 ||  5 | 李依依    |      1 |   17 |     3 | 语文   |    90 |+----+-----------+--------+------+-------+--------+-------+5 rows in set (0.000 sec)MariaDB [school]> 

3.从库查看数据表

[root@node01 ~]# hostnamenode01[root@node01 ~]# mariadbWelcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 12Server version: 10.6.11-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)]> select * from school.student;+----+-----------+--------+------+-------+--------+-------+| id | name      | gender | age  | class | course | grade |+----+-----------+--------+------+-------+--------+-------+|  1 | 高峰      |      0 |   22 |     4 | 英语   |   100 ||  2 | 陈林      |      1 |   15 |     5 | 化学   |    99 ||  3 | 王明      |      0 |   16 |     2 | 数学   |    88 ||  4 | 万易      |      0 |   17 |     4 | 地理   |    79 ||  5 | 李依依    |      1 |   17 |     3 | 语文   |    90 |+----+-----------+--------+------+-------+--------+-------+5 rows in set (0.000 sec)MariaDB [(none)]> 

以上就是Mariadb数据库主从复制同步配置过程实例的详细内容，更多关于Mariadb主从复制同步配置的资料请关注其它相关文章！