MySQL 是最流行的關系型資料庫管理系統

MySQL 配置主備模式

基于一台伺服器的資料複制,故得名單機熱備,主-備 Active-Standby 主-備方式,即指的是一台伺服器處于某種業務的激活狀态(即Active狀态),另一台伺服器處于該業務的備用狀态(即Standby狀态),主資料庫資料更新後,備份伺服器同步資料隻本機

兩台同步執行: 安裝Mariadb,設定初始密碼,啟動服務,并設定開機自啟動,這裡的配置步驟應在兩台主機上同步執行

yum install -y mariadb mariadb-server
systemctl restart mariadb
mysql_secure_installation
複制代碼

修改MariaDB主配置檔案,寫入log-bin參數開啟二進制日志檔案,然後寫入server-id指定本台MariaDB伺服器的ID号(注意：這裡主從不要重複)

vim /etc/my.cnf

[mysqld]
log-bin=mysql-bin            #開啟二進制日志檔案
server-id=12                 #本台MySQL伺服器ID(主從不能重複)
systemctl restart mariadb    #重新開機生效
複制代碼

主資料庫執行: 進入主MariaDB資料庫,授權給從資料庫登陸的賬号,這裡顯示二進制日志檔案大小并記錄下來

grant replication slave on *.* to "使用者名"@'%' identified by "密碼";    #建立用于從伺服器登陸的賬号

show master status;                                                   #顯示日志狀态

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 245 | | |
+------------------+----------+--------------+------------------+
row in set (0.00 sec)
複制代碼

從資料庫執行: 進入從MariaDB資料庫,使用主資料庫授權的使用者名,密碼,以及相應的日志檔案名稱 master_log_file,和日志檔案位置 master_log_pos,此處在主資料庫上擷取即可

change master to master_host='伺服器IP',master_user='授權賬号',master_password='密碼',master_log_file='日志檔案',master_log_pos=檔案位置;

start slave;             #啟用同步
show slave status\G;     #檢視同步狀态

注意：兩個狀态同時開啟即可
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
複制代碼

MySQL 配置主主模式

基于兩台伺服器的複制方式較多,故得名雙機熱備,雙主機方式即指兩種不同業務分别在兩台伺服器上互為主備狀态(即 Active-Standby和Standby-Active狀态),這裡需要注意的是主-主同步配置,其實就是兩台MariaDB資料庫互為主從,是以,要在兩台主機間作兩次相同的操作

兩台同步執行: 安裝MariaDB,設定初始密碼,啟動服務,并設定開機自啟動,這裡的配置步驟應在兩台主機上同步執行

yum install -y mariadb mariadb-server
systemctl restart mariadb
mysql_secure_installation
複制代碼

将MariaDB1的資料拷貝到MariaDB2上,實作資料的一緻性,(注意：這裡應先鎖定表結構,防止資料不一緻)

flush tables with read lock;                      #鎖定資料表為隻讀模式
mysqldump -u root -p --all-databases >back.sql    #備份資料庫

scp back.sql [email protected]:/root              #将資料傳遞到遠端主機
unlock tables;                                    #解除表鎖定
mysql -uroot -p <back.sql                         #從資料庫恢複資料
複制代碼

修改MariaDB主配置檔案(注意：兩台都要修改),開啟二進制日志和制定伺服器ID号

vim /etc/my.cnf

[mysqld]
log-bin=mysql-bin                                  #開啟二進制日志
server-id=10                                       #指定伺服器ID号(兩台不允許重複)
replicate-do-db=test                               #要同步的資料庫
binlog-ignore-db=mysql                             #不需要同步的資料庫
binlog-ignore-db=information_schema                #不需要同步的資料庫
auto-increment-increment=2                         #每次自增長2
auto-increment-offset=1                            #自增長從1開始

systemctl restart mariadb
複制代碼

MySQL1執行: 進入 MariaDB1 資料庫,建立用于從資料庫登陸的賬号和密碼

grant replication slave on *.* to '使用者名'@'%' identified by '密碼';    #建立用于從資料庫登陸的使用者
show master status;                                                   #檢視二進制檔案名
複制代碼

進入 MariaDB2 資料庫,同步資料

change master to master_host='主伺服器IP',master_user='授權賬号',master_password='密碼',master_log_file='二進制文名',master_log_pos=檔案空間大小;

start slave;            #啟動同步功能
show slave status\G;    #檢視同步狀态
複制代碼

MySQL2執行: 進入 MariaDB2 資料庫,建立用于從資料庫登陸的賬号和密碼

grant replication slave on *.* to '使用者名'@'%' identified by '密碼';    #建立用于從資料庫登陸的使用者
show master status;                                                   #檢視二進制檔案名
複制代碼

進入 MariaDB1 資料庫,同步資料

change master to master_host='主伺服器IP',master_user='授權賬号',master_password='密碼',master_log_file='二進制文名',master_log_pos=檔案空間大小;

start slave;            #啟動同步功能
show slave status\G;    #檢視同步狀态
複制代碼

MySQL 配置主主從模式

主-主-從,字面意思就是,兩個主一個從,主要用于資料備份,一次性備份兩個主資料庫資源,這樣一個從資料庫,就可以同時備份多個主資料庫了,節約了寶貴的伺服器資源

兩台主資料庫執行: 兩台主伺服器都需要安裝MySQL初始化并設定密碼

yum install -y mariadb mariadb-server
systemctl restart mariadb
mysql_secure_installation
複制代碼

兩台主伺服器都應配置以下内容（兩台主MySQL都需要配置）

vim /etc/my.cnf              #修改配置檔案

寫入以下内容↓
log-bin=mysql-bin            #開啟二進制日志
server-id=147                #伺服器ID号

systemctl restart mariadb    #重新開機服務
複制代碼

登陸主MySQL并建立使用者從伺服器登陸的使用者（兩台主MySQL都需要配置）

mysql -uroot –p                                                        #登陸MySQL
grant replication slave on *.* to '授權使用者'@'%' identified by '密碼';   #建立從伺服器使用者
show master status;                                                    #顯示日志狀态
複制代碼

從資料庫執行: 切記從MariaDB資料庫不能啟動,修改從MySQL主配置檔案

vim /etc/my.cnf

寫入以下内容↓

[mysqld_multi]
mysqld=/usr/bin/mysqld_safe
mysqladmin=/usr/bin/mysqladmin
log=/tmp/multi.log

[mysqld147]                            #名稱後面加數字,後期啟動用(不應相同)
port=3306                              #端口号(不應相同)
pid-file=/var/lib/mysqla/mysqld.pid    #MySQL程序号儲存位置
datadir=/var/lib/mysqla                #資料檔案儲存位置
socket=/var/lib/mysqla/mysql.sock      #指定登陸套接字
server-id=110                          #ID号,同一主從環境不能相同
user=mysql                             #MySQL登陸使用者名

[mysqld148]                            #名稱後面加數字,後期啟動用(不應相同)
port=3307                              #端口号(不應相同)
pid-file=/var/lib/mysqlb/mysqld.pid    #MySQL程序号儲存位置
datadir=/var/lib/mysqlb                #資料檔案儲存位置    
socket=/var/lib/mysqlb/mysql.sock      #指定登陸套接字
server-id=110                          #ID号,同一主從環境不能相同
user=mysql                             #MySQL登陸使用者名
複制代碼

初始化資料庫

mysql_install_db --datadir=/var/lib/mysqla --user=mysql    #初始化資料庫a
mysql_install_db --datadir=/var/lib/mysqlb --user=mysql    #初始化資料庫b
複制代碼

給相應目錄賦予權限

cd /var/lib/
chown -R mysql:mysql /var/lib/mysqla/    #修改a檔案權限
chown -R mysql:mysql /var/lib/mysqlb/    #修改b檔案權限
複制代碼

啟動相應伺服器

mysqld_multi --defaults-file=/etc/my.cnf start 147    #開啟147伺服器
mysqld_multi --defaults-file=/etc/my.cnf start 148    #開啟148伺服器
複制代碼

登陸147和148資料庫确定主從關系

mysql -P 3306 -S /var/lib/mysqla/mysql.sock    #登陸147資料庫
mysql -P 3306 -S /var/lib/mysqlb/mysql.sock    #登陸148資料庫

change master to master_host='伺服器IP',master_user='授權賬号',master_password='密碼',master_log_file='日志檔案',master_log_pos=檔案位置;
start slave;									    #啟用同步
show slave status\G;								#檢視同步狀态
複制代碼

MySQL 實作讀寫分離

MyCat是一個開源的,面向企業應用開發的大資料庫叢集,支援事務、ACID、可以替代MySQL的加強版資料庫, 其功能有可以視為MySQL叢集的企業級資料庫,用來替代昂貴的Oracle叢集.融合了記憶體緩存技術、NoSQL技術、HDFS大資料的新型SQLServer,結合傳統資料庫和新型分布式資料倉庫的新一代企業級資料庫産品.

192.168.1.5  MyCat   Server

192.168.1.11 Master  主MariaDB
192.168.1.12 Slave1  從1
192.168.1.13 Slave2  從2
複制代碼

1.在配置讀寫分離前請確定你的主機有四台,分别在每台主機安裝MariaDB資料庫,并初始化,需要注意的是MyCat服務端的MariaDB不需要初始化.

[root@localhost ~]# yum install -y mariadb mariadb-server
[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# systemctl enable mariadb
複制代碼

2.在MyCat服務端安裝JDK環境,由于MyCat是使用Java開發的,在這裡我們要先配置一下JDK環境.

[root@localhost ~]# tar -xzvf jdk-8u171-linux-x64.tar.gz -C /usr/local/
[root@localhost ~]# mv /usr/local/jdk1.8.0_171/ /usr/local/jdk
[root@localhost ~]# vim /etc/profile

#------------------------------------------------------------------
#JAVA-JDK-PATH
export JAVA_HOME=/usr/local/jdk
export JAVA_BIN=/usr/local/jdk/bin
export PATH=$PATH:$JAVA_HOME/bin
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar
export JAVA_HOME JAVA_BIN PATH CLASSPATH
#------------------------------------------------------------------

[root@localhost ~]# source /etc/profile
[root@localhost ~]# java -version
java version "1.8.0_171"
Java(TM) SE Runtime Environment (build 1.8.0_171-b11)
Java HotSpot(TM) 64-Bit Server VM (build 25.171-b11, mixed mode)
複制代碼

3.此步驟需要進入每一個資料庫建立授權使用者,也就是給MyCAT準備的使用資料庫的授權使用者.

[root@localhost ~]# mysql -uroot -p
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (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)]> create database MyCatDB;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> grant all on MyCatDB.* to "MyCat"@"%" identified by "123123";
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> grant all on MyCatDB.* to "MyCat"@"localhost" identified by "123123";
Query OK, 0 rows affected (0.00 sec)
複制代碼

4.解壓MyCat工具,并修改MyCat使用者授權檔案,寫入MyCat登陸賬戶,這裡配置檔案有很多選項,我們不需要動,隻需要修改以下幾個地方就行.

[root@localhost ~]# wget http://dl.mycat.io/1.6.5/Mycat-server-1.6.5-release-20180122220033-linux.tar.gz
[root@localhost ~]# tar -xzvf Mycat-server-1.6.5-release-20180122220033-linux.tar.gz -C /usr/local/
[root@localhost ~]# vim /usr/local/mycat/conf/server.xml

        <user name="root" defaultAccount="true">			#指定MyCat登陸使用者名
                <property name="password">123456</property>		#指定密碼
                <property name="schemas">MyCatDB</property>		#指定同步資料庫
        </user>

        <user name="user">						#指定MyCat登陸使用者名
                <property name="password">123456</property>		#指定密碼
                <property name="schemas">MyCatDB</property>		#指定同步資料庫
                <property name="readOnly">true</property>		#指定隻讀
        </user>
複制代碼

5.修改MyCat讀寫分離政策,這裡我們删除源檔案,并自己建立一個,寫入以下内容.

[root@localhost ~]# vim /usr/local/mycat/conf/schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

<schema name="MyCatDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn_test"> </schema>
<dataNode name="dn_test" dataHost="localhost" database="MyCatDB" />

<dataHost name="localhost" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                
                <heartbeat>select user()</heartbeat>
                <writeHost host="Master1" url="192.168.1.11:3306" user="MyCat" password="123123">			#配置寫主機
                <readHost host="Slave1" url="192.168.1.12:3306" user="MyCat" password="123123" />			#配置讀主機
		<readHost host="Slave2" url="192.168.1.13:3306" user="MyCat" password="123123" />			#配置讀主機
                </writeHost>
        </dataHost>
</mycat:schema>
複制代碼

6.啟動MyCat若無報錯則說明啟動成功,檢視端口看虛拟端口是否開啟成功.

[root@localhost ~]# /usr/local/mycat/bin/mycat start
[root@localhost ~]# netstat -an |grep "9066"                 #虛拟schema管理端口
[root@localhost ~]# netstat -an |grep "8066"                 #虛拟schema登陸端口
複制代碼

7.在MyCat服務主機登陸資料庫,測試同步效果.

[root@localhost ~]# mysql -uroot -p123456 -h 127.0.0.1 -P 9066

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.29-mycat-1.6.5-release-20180122220033 MyCat Server (monitor)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show @@heartbeat;                                                       #RS_CODE為1表示心跳正常
+---------+-------+--------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME    | TYPE  | HOST         | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME    | STOP  |
+---------+-------+--------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| Master1 | mysql | 192.168.1.11 | 3306 |      -1 |     0 | idle   |       0 | 0,16,16      | 2018-12-21 01:29:43 | false |
| Slave1  | mysql | 192.168.1.12 | 3306 |      -1 |     0 | idle   |       0 | 34,31,31     | 2018-12-21 01:29:43 | false |
| Slave2  | mysql | 192.168.1.13 | 3306 |      -1 |     0 | idle   |       0 | 1,16,16      | 2018-12-21 01:29:43 | false |
+---------+-------+--------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
3 rows in set (0.00 sec)

MySQL [(none)]> show @@datasource;                                                      #檢視讀寫分離的機器配置情況
+----------+---------+-------+--------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME    | TYPE  | HOST         | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+---------+-------+--------------+------+------+--------+------+------+---------+-----------+------------+
| dn_test  | Master1 | mysql | 192.168.1.11 | 3306 | W    |      0 |    0 | 1000 |       0 |         0 |          0 |
| dn_test  | Slave1  | mysql | 192.168.1.12 | 3306 | R    |      0 |    0 | 1000 |       0 |         0 |          0 |
| dn_test  | Slave2  | mysql | 192.168.1.13 | 3306 | R    |      0 |    0 | 1000 |       0 |         0 |          0 |
+----------+---------+-------+--------------+------+------+--------+------+------+---------+-----------+------------+
3 rows in set (0.01 sec)

MySQL [(none)]>
複制代碼

8.登入MyCat代理端,測試讀寫分離服務.

[root@localhost ~]# mysql -uroot -p123456 -h127.0.0.1 -P 8066

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.29-mycat-1.6.5-release-20180122220033 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]>
MySQL [(none)]>
MySQL [(none)]>
MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| MyCatDB  |
+----------+
1 row in set (0.00 sec)

MySQL [(none)]>
複制代碼

MySQL 增加監控

1.首先我們先來下載下傳MyCat以及Zookeeper.

[root@localhost ~]# wget http://www-eu.apache.org/dist/zookeeper/zookeeper-3.4.12/zookeeper-3.4.12.tar.gz
[root@localhost ~]# wget http://dl.mycat.io/mycat-web-1.0/Mycat-web-1.0-SNAPSHOT-20170102153329-linux.tar.gz
複制代碼

2.在MyCat服務端安裝JDK環境,由于MyCat是使用Java開發的,在這裡我們要先配置一下JDK環境.

[root@localhost ~]# tar -xzvf jdk-8u171-linux-x64.tar.gz -C /usr/local/
[root@localhost ~]# mv /usr/local/jdk1.8.0_171/ /usr/local/jdk
[root@localhost ~]# vim /etc/profile

#------------------------------------------------------------------
#JAVA-JDK-PATH
export JAVA_HOME=/usr/local/jdk
export JAVA_BIN=/usr/local/jdk/bin
export PATH=$PATH:$JAVA_HOME/bin
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar
export JAVA_HOME JAVA_BIN PATH CLASSPATH
#------------------------------------------------------------------

[root@localhost ~]# source /etc/profile
[root@localhost ~]# java -version
java version "1.8.0_171"
Java(TM) SE Runtime Environment (build 1.8.0_171-b11)
Java HotSpot(TM) 64-Bit Server VM (build 25.171-b11, mixed mode)
複制代碼

3.配置MariaDB開啟lower_case_table_names選項,每台都要開啟.

[root@localhost ~]# yum install -y mariadb mariadb-server
[root@localhost ~]# vim /etc/my.cnf

[mysqld]
lower_case_table_names = 1

[root@localhost ~]# systemctl restart mariadb
複制代碼

4.安裝zookeeper,其主要用來統計資料.

[root@localhost ~]# tar -xzvf zookeeper-3.4.12.tar.gz -C /usr/local/
[root@localhost ~]# cd /usr/local/zookeeper-3.4.12/conf/
[root@localhost ~]# cp zoo_sample.cfg zoo.cfg
[root@localhost ~]# vim zoo.cfg

dataDir=/usr/local/...
dataLogDir=/usr/local/...
複制代碼

5.運作zookeeper

[root@localhost ~]# cd /usr/local/zookeeper-3.4.12/bin/
[root@localhost ~]# ./zkServer.sh start
[root@localhost ~]# netstat -ant | grep 2181
tcp  0    0 :::2181       :::*           LISTEN 

如果出現錯誤：nohup: failed to run command `java’: No such file or directory
可以在zkServer.sh中的首行添加如下代碼

export JAVA_HOME=/usr/lib/jdk
export PATH=$JAVA_HOME/bin:$PATH
複制代碼

6.最後一步,安裝并運作Mycat-Web

[root@localhost ~]# tar -xzvf Mycat-web-1.0-SNAPSHOT-20170102153329-linux.tar.gz -C /usr/local
[root@localhost ~]# cd /usr/local/mycat-web
[root@localhost ~]# ./start.sh &
[root@localhost ~]# netstat  -an | grep "8082"
tcp  0    0 :::8082       :::*           LISTEN 

[root@localhost ~]# curl http://localhost:8082/mycat
複制代碼

轉載連結：https://juejin.cn/post/7179906588549840955

MySQL 資料庫讀寫分離

MySQL 配置主備模式

MySQL 配置主主模式

MySQL 配置主主從模式

MySQL 實作讀寫分離

MySQL 增加監控

繼續閱讀

2022秋招面試總結（cpp+java+測開）百度測開一面位元組後端一面蝦皮後端一面蝦皮後端二面

資料庫之DDL操作資料庫DDL操作資料庫DDL操作資料表

資料庫之DQL操作資料庫

mysql優化（sql優化）

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案