文章詳情頁

Mariadb數(shù)據(jù)庫主從復(fù)制同步配置過程實(shí)例

瀏覽：198日期：2023-03-30 13:21:07

一、環(huán)境規(guī)劃

hostnameIP地址系統(tǒng)版本角色master192.168.3.171centos 7.6主節(jié)點(diǎn)node01192.168.3.172centos 7.6從節(jié)點(diǎn)node02192.168.3.173centos 7.6從節(jié)點(diǎn)

二、Mariadb的主從復(fù)制介紹

1.主從復(fù)制簡介

主從同步使得數(shù)據(jù)可以從一個(gè)數(shù)據(jù)庫服務(wù)器復(fù)制到其他服務(wù)器上，在復(fù)制數(shù)據(jù)時(shí)，一個(gè)服務(wù)器充當(dāng)主服務(wù)器（master），其余的服務(wù)器充當(dāng)從服務(wù)器（slave）。

2.半同步復(fù)制介紹

半同步復(fù)制是解決主庫數(shù)據(jù)掛掉，從庫數(shù)據(jù)不一致的問題。解決方法：半同步復(fù)制，半同步復(fù)制場景中，master會監(jiān)視所有的slave，確保其中至少一臺數(shù)據(jù)完全同步成功，master才會返回客戶端信息，此數(shù)據(jù)寫成功。

3.主從復(fù)制原理圖

三、安裝Mariadb

1.配置yum倉庫

3個(gè)節(jié)點(diǎn)都安裝Mariadb數(shù)據(jù)庫

[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服務(wù)

[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.從節(jié)點(diǎn)安裝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    #  一組主從組里的每個(gè)id必須是唯一值。推薦用ip位數(shù)log-bin= mysql-bin # 二進(jìn)制日志，后面指定存放位置。如果只是指定名字，默認(rèn)存放在/var/lib/mysql下lower_case_table_names=1 # 不區(qū)分大小寫binlog-format=ROW    # 二進(jìn)制日志文件格式log-slave-updates=True    # slave更新是否記入日志sync-master-info=1    # 值為1確保信息不會丟失slave-parallel-threads=3 #同時(shí)啟動多少個(gè)復(fù)制線程，最多與要復(fù)制的數(shù)據(jù)庫數(shù)量相等即可binlog-checksum=CRC32    # 效驗(yàn)碼master-verify-checksum=1    # 啟動主服務(wù)器效驗(yàn)slave-sql-verify-checksum=1   # 啟動從服務(wù)器效驗(yàn)[galera][embedded][mariadb][mariadb-10.6][root@master ~]#

3.重啟mariadb服務(wù)

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

五、mariadb從庫配置

1.修改從庫node01節(jié)點(diǎn)的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是二進(jìn)制文件relay_log = relay-bin    # 中繼日志, 后面指定存放位置。如果只是指定名字，默認(rèn)存放在/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節(jié)點(diǎn)的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是二進(jìn)制文件relay_log = relay-bin    # 中繼日志, 后面指定存放位置。如果只是指定名字，默認(rèn)存放在/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服務(wù)

systemctl restart mariadb

六、查看mariadb主庫狀態(tài)

1.創(chuàng)建數(shù)據(jù)庫用戶

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.查看數(shù)據(jù)庫用戶信息

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.查看主庫狀態(tài)

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.從庫設(shè)置主庫的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.查詢從庫狀態(tài)

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.主庫寫入數(shù)據(jù)

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", "化學(xué)", "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", "數(shù)學(xué)", "88"),  ( "萬易", "0", "17", "4", "地理", "79" ), ( "李依依", "1", "17", "3", "語文",  "90"  ) ;Query OK, 3 rows affected (0.017 sec)Records: 3  Duplicates: 0  Warnings: 0

2.主庫查看數(shù)據(jù)表

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

3.從庫查看數(shù)據(jù)表

[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 | 化學(xué)   |    99 ||  3 | 王明      |      0 |   16 |     2 | 數(shù)學(xué)   |    88 ||  4 | 萬易      |      0 |   17 |     4 | 地理   |    79 ||  5 | 李依依    |      1 |   17 |     3 | 語文   |    90 |+----+-----------+--------+------+-------+--------+-------+5 rows in set (0.000 sec)MariaDB [(none)]>

以上就是Mariadb數(shù)據(jù)庫主從復(fù)制同步配置過程實(shí)例的詳細(xì)內(nèi)容，更多關(guān)于Mariadb主從復(fù)制同步配置的資料請關(guān)注其它相關(guān)文章！

MariaDB

上一條：用表單來提交sql（轉(zhuǎn)）3下一條：centos編譯安裝mariadb的詳細(xì)過程

排行榜

					
					Mysql入門系列：MYSQL列類型選擇與MYSQL查詢效率
SQL Server數(shù)據(jù)庫開發(fā)之觸發(fā)器的實(shí)際應(yīng)用
用腳本和查詢主動監(jiān)視Oracle 9i性能
傳甲骨文將增加對MySQL投資與微軟競爭
整理Oracle數(shù)據(jù)庫碎片
MySql中sql語句執(zhí)行過程詳細(xì)講解
MySQL慢查詢以及解決方案詳解
Oracle 10g DBMS_SCHEDULER的中度解析
Oracle 數(shù)據(jù)庫集中復(fù)制方法逐步精細(xì)
Oracle數(shù)據(jù)庫優(yōu)化及其應(yīng)用程序研究
Oracle數(shù)據(jù)庫物理文件大小的限制簡介
				