目錄
- 一.前言
- 二.關于MySQL主從同步
- 三.部署規劃
- 3.1 服務器規劃
- 3.2 資料庫目錄規劃
- 四.準備工具
- 五.四臺機器上使用通用二進制包安裝MySQL（以node7為例）
- 5.1 上傳MySQL通用二進制安裝包到node7的/usr/local/src目錄下
- 5.2 解壓MySQL到指定目錄并改名
- 5.3 創建MySQL用戶和用戶組
- 5.4 配置MySQL的bin目錄到PATH路徑
- 5.5 創建MySQL資料存放目錄
- 5.6 配置MySQL組態檔
- 5.7 初始化MySQL資料庫
- 5.8 生成ssl（可選）
- 5.9 配置MySQL啟動項并設定開機自啟動
- 5.9.1 centos6版本
- 5.9.2 centos7版本
- 5.10 啟動MySQL
- 5.11 進行MySQL安全初始化(可選)
- 5.12 修改密碼，給用戶賦權限（根據自己情況賦權限）
- 5.13 匯入時區資訊到MySQL庫
- 5.14 查看MySQL版本資訊
- 5.15 如果防火墻開著，則需要開放3306埠
- 5.16 利用logrotate對MySQL日志進行輪轉(日志自動備份切割)
- 六.MySQL主從同步之一主多從架構
- 6.1 服務器規劃
- 6.2 主從同步的原理
- 6.3 部署MySQL主從同步之一主多從
- 6.3.1 配置主資料庫服務器node7
- 6.3.1.1 創建需要同步的資料庫及其表
- 6.3.1.2 修改MySQL組態檔
- 6.3.1.3 主庫給從庫授予replication權限
- 6.3.1.4 備份主庫需要從庫同步的資料庫hotdata
- 6.3.2 配置從資料庫服務器node8
- 6.3.2.1 檢查資料庫版本
- 6.3.2.2 測驗連接到主服務器是否成功
- 6.3.2.3 匯入hotdata資料庫，和主資料庫保持一致
- 6.3.2.4 修改組態檔
- 6.3.2.5 從庫設定slave復制主庫資料
- 6.3.3 配置從資料庫服務器node9
- 6.3.4 在主服務器上查看狀態
- 6.3.5 插入資料測驗主從同步
- 6.3.6 徹底取消主從同步
- 6.3.1 配置主資料庫服務器node7
- 6.4 總結
- 七.MySQL主從同步之主主雙向同步架構
- 7.1 服務器規劃
- 7.2 主從同步的原理
- 7.3 部署MySQL主從同步之主主雙向同步
- 7.3.1 配置資料庫服務器node7
- 7.3.1.1 創建需要同步的資料庫及其表
- 7.3.1.2 修改MySQL組態檔
- 7.3.1.3 給node8授予replication的權限
- 7.3.2 配置資料庫服務器node8
- 7.3.2.1 創建需要同步的資料庫及其表
- 7.3.2.2 修改MySQL組態檔
- 7.3.2.3 測驗從賬號slave能否登陸node7
- 7.3.2.4 給node7賦予replication權限，并設定node8復制node7
- 7.3.3 配置資料庫服務器node7
- 7.3.3.1 測驗從賬號slave能否登陸node8
- 7.3.3.2 設定node7復制node8
- 7.3.4 插入資料測驗主從是否同步
- 7.3.4.1 在node7上插入資料，查看node8 上有沒有同步資料
- 7.3.4.2 在node8上插入資料，查看node7 上有沒有同步資料
- 7.3.5 徹底取消主從同步
- 7.3.1 配置資料庫服務器node7
- 7.4 總結
- 八.MySQL主從同步之M-S-S架構
- 8.1 服務器規劃
- 8.2 主從同步的原理
- 8.3 部署MySQL主從同步之M-S-S
- 8.3.1 配置主資料庫服務器node7
- 8.3.1.1 創建需要同步的資料庫及其表
- 8.3.1.2 授予node8 replication的權限
- 8.3.1.3 修改組態檔并重啟
- 8.3.2 配置slave中繼node8
- 8.3.2.1 創建需要同步的資料庫及其表
- 8.3.2.2 修改組態檔并重啟
- 8.3.2.3 授權node8復制node7,并授予node9 replication的權限
- 8.3.3 配置從服務器node9
- 8.3.3.1 創建需要同步的資料庫及其表
- 8.3.3.2 修改組態檔并重啟
- 8.3.3.3 指定node8為node9的主
- 8.3.4 插入資料測驗
- 8.3.1 配置主資料庫服務器node7
- 8.4 總結
- 九.MySQL主從同步之多主多從架構
- 9.1 服務器規劃
- 9.2 MySQL多主多從架構圖
- 9.3 主從同步的原理
- 9.4 部署MySQL主從同步之雙主雙從
- 9.4.1 配置主資料庫服務器node6
- 9.4.1.1 創建需要同步的資料庫及其表
- 9.4.1.2 修改node6的MySQL組態檔
- 9.4.1.3 node6創建復制賬號并授權給node7和node8
- 9.4.2 配置主資料庫服務器node7
- 9.4.2.1 創建需要同步的資料庫及其表
- 9.4.2.2 修改node7的MySQL組態檔
- 9.4.2.3 node7創建復制賬號并授權給node6和node9
- 9.4.3 配置從資料庫服務器node8
- 9.4.3.1 創建需要同步的資料庫及其表
- 9.4.3.2 修改node8的MySQL組態檔
- 9.4.4 配置從資料庫服務器node9
- 9.4.4.1 創建需要同步的資料庫及其表
- 9.4.4.2 修改node9的MySQL組態檔
- 9.4.5 在slave上配置master（所有節點）
- 9.4.6 啟動slave,讓四臺MySQL進入主從復制狀態
- 9.4.7 測驗驗證雙主雙從
- 9.4.1 配置主資料庫服務器node6
- 9.5 總結
- 十.參考資料
一.前言
? 本文將指導搭建所有的MySQL主從同步架構方案：
- ? 一主多從架構
- ? 主主雙向同步架構
- ? M-S-S三級級聯同步架構
- ? 多主多從架構
二.關于MySQL主從同步
? MySQL主從同步是構建大型，高性能應用的基礎，MySQL主從同步可以實作在從服務器可以執行查詢作業(即我們常說的讀功能)，降低主服務器壓力(主庫寫，從庫讀，降壓),在從主服務器進行備份，避免備份期間影響主服務器服務(確保資料安全),當主服務器出現問題時，可以切換到從服務器（提升性能)，
三.部署規劃
3.1 服務器規劃
| 服務器 | 作業系統版本 | CPU架構 | MySQL版本 |
|---|---|---|---|
| node6 | CentOS Linux release 7.4.1708 | x86_64 | 5.7.26 |
| node7 | CentOS Linux release 7.4.1708 | x86_64 | 5.7.26 |
| node8 | CentOS Linux release 7.4.1708 | x86_64 | 5.7.26 |
| node9 | CentOS Linux release 7.4.1708 | x86_64 | 5.7.26 |
3.2 資料庫目錄規劃
| 檔案型別 | 檔案部署位置 |
|---|---|
| 資料目錄datadir | /data/data（/data目錄請確保足夠大） |
| 組態檔my.cnf | /etc/my.cnf |
| 錯誤日志log-error | /data/log/mysql_error.log |
| 二進制日志log-bin | /data/binlogs/mysql-bin(用于資料庫恢復和主從復制，以及審計（audit）操作) |
| 慢查詢日志slow_query_log_file | /data/log/mysql_slow_query.log |
| 套接字檔案socket | /data/run/mysql.sock |
| 行程ID檔案mysql.pid | /data/run/mysql.pid |
四.準備工具
1.MySQL通用二進制包:mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
下載地址:https://dev.mysql.com/downloads/mysql/5.7.html#downloads
? 
五.四臺機器上使用通用二進制包安裝MySQL（以node7為例）
5.1 上傳MySQL通用二進制安裝包到node7的/usr/local/src目錄下
[root@node7 src]# pwd
/usr/local/src
[root@node7 src]# ls
mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
5.2 解壓MySQL到指定目錄并改名
[root@node7 src]# tar -zxf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@node7 src]# cd /usr/local/
[root@node7 local]# ls
bin etc games include lib lib64 libexec mysql-5.7.26-linux-glibc2.12-x86_64 sbin share src
[root@node7 local]# mv mysql-5.7.26-linux-glibc2.12-x86_64 mysql
[root@node7 local]# ls
bin etc games include lib lib64 libexec mysql sbin share src
5.3 創建MySQL用戶和用戶組
[root@node7 local]# groupadd -g 1111 mysql
[root@node7 local]# useradd -g mysql -u 1111 -s /sbin/nologin mysql
[root@node7 local]# id mysql #查看用戶資訊
uid=1111(mysql) gid=1111(mysql) groups=1111(mysql)
5.4 配置MySQL的bin目錄到PATH路徑
[root@node7 local]# echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
[root@node7 local]# source /etc/profile
[root@node7 local]# mysql #輸入MySQL之后雙擊tab鍵，即可列出候選MySQL命令
mysql mysql_client_test_embedded mysqld-debug mysqldumpslow mysql_plugin mysqlslap mysql_upgrade
mysqladmin mysql_config mysqld_multi mysql_embedded mysqlpump mysql_ssl_rsa_setup mysqlxtest
mysqlbinlog mysql_config_editor mysqld_safe mysqlimport mysql_secure_installation mysqltest_embedded
mysqlcheck mysqld mysqldump mysql_install_db mysqlshow mysql_tzinfo_to_sql
5.5 創建MySQL資料存放目錄
[root@node7 ~]# mkdir -p /data/{data,log,binlogs,run}
[root@node7 ~]# tree /data #如果沒有tree命令，則yum -y install tree安裝
/data
├── binlogs
├── data
├── log
└── run
4 directories, 0 files
[root@node7 ~]# chown -R mysql:mysql /data
[root@node7 ~]# ll /data/
total 0
drwxr-xr-x 2 mysql mysql 6 Dec 3 11:07 binlogs
drwxr-xr-x 2 mysql mysql 6 Dec 3 11:07 data
drwxr-xr-x 2 mysql mysql 6 Dec 3 11:07 log
drwxr-xr-x 2 mysql mysql 6 Dec 3 11:07 run
5.6 配置MySQL組態檔
[root@node7 mysql]# rm -rf /etc/my.cnf
[root@node7 mysql]# touch /etc/my.cnf
#my.cnf組態檔詳解，請查看我上一篇blog的#https://www.cnblogs.com/renshengdezheli/p/11913248.html的“MySQL組態檔優化參考”
[root@node7 mysql]# cat /etc/my.cnf
[client]
port=3306
socket=/data/run/mysql.sock
[mysqld]
port=3306
socket=/data/run/mysql.sock
pid_file=/data/run/mysql.pid
datadir=/data/data
default_storage_engine=InnoDB
max_allowed_packet=512M
max_connections=2048
open_files_limit=65535
skip-name-resolve
lower_case_table_names=1
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
innodb_buffer_pool_size=1024M
innodb_log_file_size=2048M
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=0
key_buffer_size=64M
log-error=/data/log/mysql_error.log
log-bin=/data/binlogs/mysql-bin
slow_query_log=1
slow_query_log_file=/data/log/mysql_slow_query.log
long_query_time=5
tmp_table_size=32M
max_heap_table_size=32M
query_cache_type=0
query_cache_size=0
server-id=1
5.7 初始化MySQL資料庫
[root@node7 mysql]# mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/data
[root@node7 mysql]# echo $?
0
[root@node7 mysql]# grep 'temporary password' /data/log/mysql_error.log #查看MySQL初始化密碼
2019-12-03T03:47:42.639938Z 1 [Note] A temporary password is generated for root@localhost: lhrh>J,p<8gw
5.8 生成ssl（可選）
#關于MySQL開啟ssl查看https://www.cnblogs.com/mysql-dba/p/7061300.html
[root@node7 mysql]# mysql_ssl_rsa_setup --basedir=/usr/local/mysql --datadir=/data/data
Generating a 2048 bit RSA private key
......................................+++
.+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
....................................+++
............................+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
.....................................................................................+++
..............................................+++
writing new private key to 'client-key.pem'
-----
#執行完成之后，會有在datadir目錄生成*.pem檔案
[root@node7 mysql]# ls /data/data/
auto.cnf client-cert.pem ibdata1 mysql public_key.pem sys
ca-key.pem client-key.pem ib_logfile0 performance_schema server-cert.pem
ca.pem ib_buffer_pool ib_logfile1 private_key.pem server-key.pem
5.9 配置MySQL啟動項并設定開機自啟動
5.9.1 centos6版本
cd /usr/local/mysql
cp support-files/mysql.server /etc/init.d/mysql.server
chkconfig --add mysql.server
chkconfig mysql.server on
chkconfig --list
5.9.2 centos7版本
[root@node7 system]# cd /usr/lib/systemd/system
[root@node7 system]# touch mysqld.service
[root@node7 system]# vim mysqld.service
[root@node7 system]# cat mysqld.service
# Copyright (c) 2015, 2016, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#
# systemd service file for MySQL forking server
#
[Unit]
Description=MySQL Server
Documentation=man:mysqld(5.7)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
Type=forking
PIDFile=/data/run/mysql.pid
# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0
# Execute pre and post scripts as root
PermissionsStartOnly=true
# Needed to create system tables
#ExecStartPre=/usr/bin/mysqld_pre_systemd
# Start main service
ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid $MYSQLD_OPTS
# Use this to switch malloc implementation
EnvironmentFile=-/etc/sysconfig/mysql
# Sets open_files_limit
LimitNOFILE = 65535
Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp=false
[root@node7 system]# systemctl daemon-reload #重新加載服務組態檔
[root@node7 system]# systemctl enable mysqld #設定MySQL開機自啟動
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.
[root@node7 system]# systemctl is-enabled mysqld #查看MySQL開機自啟動是否設定成功
enabled
5.10 啟動MySQL
[root@node7 system]# systemctl start mysqld
[root@node7 system]# systemctl status mysqld #查看MySQL啟動狀態
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2019-12-03 14:42:14 CST; 9s ago
Docs: man:mysqld(5.7)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 2905 ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Main PID: 2907 (mysqld)
CGroup: /system.slice/mysqld.service
└─2907 /usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid
Dec 03 14:42:13 node7 systemd[1]: Starting MySQL Server...
Dec 03 14:42:14 node7 systemd[1]: Started MySQL Server.
[root@node7 system]# ps -ef | grep mysql #查看MySQL行程
mysql 2907 1 2 14:42 ? 00:00:00 /usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid
root 2942 2576 0 14:42 pts/0 00:00:00 grep --color=auto mysql
5.11 進行MySQL安全初始化(可選)
[root@node7 system]# mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root: #這里輸入MySQL初始化時生成的密碼（grep 'temporary password' /data/log/mysql_error.log）
The existing password for the user account root has expired. Please set a new password.
New password: #輸入新密碼
Re-enter new password:
VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?
Press y|Y for Yes, any other key for No: n #y安裝MySQL密碼插件
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n
... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL 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? (Press y|Y for Yes, any other key for No) : y #y移除匿名用戶
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? (Press y|Y for Yes, any other key for No) : n #是否允許root遠程登錄
... skipping.
By default, MySQL 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? (Press y|Y for Yes, any other key for No) : y #是否移除test資料庫
- 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? (Press y|Y for Yes, any other key for No) : y #重繪權限表
Success.
All done!
5.12 修改密碼，給用戶賦權限（根據自己情況賦權限）
[root@node7 ~]# mysql -uroot -p123456
mysql> SET PASSWORD = PASSWORD('123456');#修改root密碼為123456，如果提示ERROR 1819 (HY000): Your password does not satisfy the current policy requirements，則說明密碼設定太簡單，如果想設定123456這樣的簡單密碼，可在SQL中執行：
#mysql> set global validate_password_policy=0;
#mysql> set global validate_password_length=1;
#這樣再次執行SET PASSWORD = PASSWORD('123456')就可成功，
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> UPDATE mysql.user SET authentication_string =PASSWORD('123456') WHERE User='mysql'; #修改MySQL的mysql用戶的密碼為123456
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 1
mysql> GRANT ALL PRIVILEGES ON *.* TO mysql@localhost IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO mysql@"%" IDENTIFIED BY '123456' WITH GRANT OPTION; #賦予mysql用戶可以在任何機器上登錄，并擁有所有表的所有權限
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.07 sec)
mysql> FLUSH PRIVILEGES ; #重繪權限，讓修改立即生效
Query OK, 0 rows affected (0.00 sec)
mysql> exit;
Bye
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
#以下是為MySQL賦權限的介紹
mysql> grant 權限1,權限2,…權限n on 資料庫名稱.表名稱 to 用戶名@用戶地址 identified by ‘連介面令’;
權限1,權限2,…權限n代表select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file等14個權限，
當權限1,權限2,…權限n被all privileges或者all代替，表示賦予用戶全部權限，
當資料庫名稱.表名稱被*.*代替，表示賦予用戶操作服務器上所有資料庫所有表的權限，
用戶地址可以是localhost，也可以是ip地址、機器名字、域名，也可以用’%'表示從任何地址連接，
‘連介面令’不能為空，否則創建失敗，
比如：
mysql>grant select,insert,update,delete,create,drop on vtdc.employee to [email protected] identified by ‘123′;
給來自10.163.225.87的用戶joe分配可對資料庫vtdc的employee表進行select,insert,update,delete,create,drop等操作的權限，并設定口令為123，
mysql>grant all privileges on vtdc.* to [email protected] identified by ‘123′;
給來自10.163.225.87的用戶joe分配可對資料庫vtdc所有表進行所有操作的權限，并設定口令為123，
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
5.13 匯入時區資訊到MySQL庫
[root@node7 system]# mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -uroot -p123456 mysql
#執行上述操作之后，time_zone，time_zone_leap_second，time_zone_name，time_zone_transition ，time_zone_transition_type表就有時區資料了
[root@node7 system]# mysql -uroot -p123456 mysql
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
5.14 查看MySQL版本資訊
[root@node7 system]# mysql -V
mysql Ver 14.14 Distrib 5.7.26, for linux-glibc2.12 (x86_64) using EditLine wrapper
[root@node7 system]# mysqladmin version -uroot -p123456
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqladmin Ver 8.42 Distrib 5.7.26, for linux-glibc2.12 on x86_64
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Server version 5.7.26-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /data/run/mysql.sock
Uptime: 31 min 53 sec
Threads: 1 Questions: 8855 Slow queries: 0 Opens: 214 Flush tables: 1 Open tables: 203 Queries per second avg: 4.628
5.15 如果防火墻開著，則需要開放3306埠
[root@node7 system]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
Active: active (running) since Tue 2019-12-03 15:22:18 CST; 3s ago
Docs: man:firewalld(1)
Main PID: 3343 (firewalld)
CGroup: /system.slice/firewalld.service
└─3343 /usr/bin/python -Es /usr/sbin/firewalld --nofork --nopid
Dec 03 15:22:17 node7 systemd[1]: Starting firewalld - dynamic firewall daemon...
Dec 03 15:22:18 node7 systemd[1]: Started firewalld - dynamic firewall daemon.
Dec 03 15:22:18 node7 firewalld[3343]: WARNING: ICMP type 'beyond-scope' is not supported by the kernel for ipv6.
Dec 03 15:22:18 node7 firewalld[3343]: WARNING: beyond-scope: INVALID_ICMPTYPE: No supported ICMP type., ignoring...-time.
Dec 03 15:22:18 node7 firewalld[3343]: WARNING: ICMP type 'failed-policy' is not supported by the kernel for ipv6.
Dec 03 15:22:18 node7 firewalld[3343]: WARNING: failed-policy: INVALID_ICMPTYPE: No supported ICMP type., ignorin...-time.
Dec 03 15:22:18 node7 firewalld[3343]: WARNING: ICMP type 'reject-route' is not supported by the kernel for ipv6.
Dec 03 15:22:18 node7 firewalld[3343]: WARNING: reject-route: INVALID_ICMPTYPE: No supported ICMP type., ignoring...-time.
Hint: Some lines were ellipsized, use -l to show in full.
#添加防火墻規則
[root@node7 system]# firewall-cmd --permanent --zone=public --add-port=3306/tcp
success
#重新加載防火墻規則
[root@node7 system]# firewall-cmd --reload
success
#檢查規則是否設定生效
[root@node7 system]# firewall-cmd --zone=public --query-port=3306/tcp
yes
#列出防火墻所有開放的埠
[root@node7 system]# firewall-cmd --list-all
public (active)
target: default
icmp-block-inversion: no
interfaces: ens33
sources:
services: ssh dhcpv6-client
ports: 3306/tcp
protocols:
masquerade: no
forward-ports:
source-ports:
icmp-blocks:
rich rules:
5.16 利用logrotate對MySQL日志進行輪轉(日志自動備份切割)
#logrotate配置詳解請查看：https://www.linuxidc.com/Linux/2019-02/157099.htm
[root@node7 ~]# touch /root/.my.cnf
[root@node7 ~]# vim /root/.my.cnf
[root@node7 ~]# cat /root/.my.cnf
[mysqladmin]
password=123456
user=root
[root@node7 ~]# chmod 600 /root/.my.cnf
[root@node7 ~]# cp /usr/local/mysql/support-files/mysql-log-rotate /etc/logrotate.d/
[root@node7 ~]# chmod 644 /etc/logrotate.d/mysql-log-rotate
[root@node7 ~]# vim /etc/logrotate.d/mysql-log-rotate
[root@node7 ~]# cat /etc/logrotate.d/mysql-log-rotate
# The log file name and location can be set in
# /etc/my.cnf by setting the "log-error" option
# in either [mysqld] or [mysqld_safe] section as
# follows:
#
# [mysqld]
# log-error=/usr/local/mysql/data/mysqld.log
#
# In case the root user has a password, then you
# have to create a /root/.my.cnf configuration file
# with the following content:
#
# [mysqladmin]
# password = <secret>
# user= root
#
# where "<secret>" is the password.
#
# ATTENTION: The /root/.my.cnf file should be readable
# _ONLY_ by root !
/data/log/mysql_*.log {
# create 600 mysql mysql
notifempty #當日志檔案為空時，不進行輪轉
daily #默認每一天執行一次rotate輪轉作業
rotate 52 #保留多少個日志檔案(輪轉幾次).默認保留四個.就是指定日志檔案洗掉之前輪轉的次數，0 指沒有備份，此處表示保留52天的日志
missingok #如果日志檔案丟失，不要顯示錯誤
compress #通過gzip 壓縮轉儲以后的日志
postrotate #執行的指令
# just if mysqld is really running
if test -x /usr/local/mysql/bin/mysqladmin && \
/usr/local/mysql/bin/mysqladmin ping &>/dev/null
then
/usr/local/mysql/bin/mysqladmin flush-logs
fi
endscript
}
[root@node7 ~]#
[root@node7 ~]# logrotate -fv /etc/logrotate.d/mysql-log-rotate #強制進行日志輪轉
reading config file /etc/logrotate.d/mysql-log-rotate
Allocating hash table for state file, size 15360 B
Handling 1 logs
rotating pattern: /data/log/mysql_*.log forced from command line (52 rotations)
empty log files are not rotated, old logs are removed
considering log /data/log/mysql_error.log
log needs rotating
considering log /data/log/mysql_slow_query.log
log needs rotating
rotating log /data/log/mysql_error.log, log->rotateCount is 52
dateext suffix '-20191203'
glob pattern '-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
renaming /data/log/mysql_error.log.52.gz to /data/log/mysql_error.log.53.gz
(t -- won't try to dispose of it
.................
renaming /data/log/mysql_slow_query.log to /data/log/mysql_slow_query.log.1
running postrotate script
compressing log with: /bin/gzip
[root@node7 ~]#
[root@node7 ~]# echo $?
0
#此時查看日志目錄，發現日志已經進行輪轉，并壓縮
[root@node7 ~]# ls /data/log/
mysql_error.log mysql_error.log.1.gz mysql_slow_query.log mysql_slow_query.log.1.gz
自此，node7上MySQL安裝完畢，node6，node8，node9上的MySQL也按照此方法安裝，
安裝MySQL是進行主從同步，讀寫分離，分表分庫配置的基礎，只有安裝了MySQL才能進行接下來的操作，
六.MySQL主從同步之一主多從架構
6.1 服務器規劃
| 主機名 | IP | 作業系統版本 | MySQL版本 | 角色 |
|---|---|---|---|---|
| node7 | 192.168.110.188 | CentOS 7.4.1708 | 5.7.26 | master（主） |
| node8 | 192.168.110.186 | CentOS 7.4.1708 | 5.7.26 | slave（從） |
| node9 | 192.168.110.187 | CentOS 7.4.1708 | 5.7.26 | slave（從） |
6.2 主從同步的原理
? master將改變記錄到二進制日志(binary log)中,slave將master的binary log events拷貝到它的中繼日志(relay log),slave重做中繼日志中的事件，修改salve上的資料，
6.3 部署MySQL主從同步之一主多從
6.3.1 配置主資料庫服務器node7
6.3.1.1 創建需要同步的資料庫及其表
[root@node7 ~]# mysql -uroot -p123456
mysql> create database hotdata; #創建熱點資料庫
Query OK, 1 row affected (0.70 sec)
mysql> use hotdata;
Database changed
#創建顧客表
mysql> create table customers(cust_id int,cust_name varchar(30),cust_address varchar(50),cust_city varchar(30),cust_state varchar(50),cust_email varchar(30),cust_country varchar(50));
Query OK, 0 rows affected (0.44 sec)
mysql> desc customers; #查看表結構
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| cust_id | int(11) | YES | | NULL | |
| cust_name | varchar(30) | YES | | NULL | |
| cust_address | varchar(50) | YES | | NULL | |
| cust_city | varchar(30) | YES | | NULL | |
| cust_state | varchar(50) | YES | | NULL | |
| cust_email | varchar(30) | YES | | NULL | |
| cust_country | varchar(50) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
7 rows in set (0.36 sec)
mysql> exit
Bye
6.3.1.2 修改MySQL組態檔
#先關閉資料庫再修改MySQL組態檔
[root@node7 ~]# systemctl stop mysqld
[root@node7 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: inactive (dead) since Thu 2019-12-05 10:59:38 CST; 8s ago
Docs: man:mysqld(5.7)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 6777 ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Main PID: 6779 (code=exited, status=0/SUCCESS)
Dec 05 10:35:44 node7 systemd[1]: Starting MySQL Server...
Dec 05 10:36:07 node7 systemd[1]: Started MySQL Server.
Dec 05 10:59:36 node7 systemd[1]: Stopping MySQL Server...
Dec 05 10:59:38 node7 systemd[1]: Stopped MySQL Server.
#修改好的組態檔如下，主從同步相關的配置都放在“#mysql replication”下面
[root@node7 ~]# vim /etc/my.cnf
[root@node7 ~]# cat /etc/my.cnf
[client]
port=3306
socket=/data/run/mysql.sock
[mysqld]
port=3306
socket=/data/run/mysql.sock
pid_file=/data/run/mysql.pid
datadir=/data/data
default_storage_engine=InnoDB
max_allowed_packet=512M
max_connections=2048
open_files_limit=65535
skip-name-resolve
lower_case_table_names=1
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
innodb_buffer_pool_size=1024M
innodb_log_file_size=2048M
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=0
key_buffer_size=64M
log-error=/data/log/mysql_error.log
slow_query_log=1
slow_query_log_file=/data/log/mysql_slow_query.log
long_query_time=5
tmp_table_size=32M
max_heap_table_size=32M
query_cache_type=0
query_cache_size=0
skip_ssl
#mysql replication，主從同步配置
#logbin引數啟用二進制日志，并把二進制日志放在/data/binlogs目錄下
log-bin=/data/binlogs/mysql-bin
#資料庫標志ID，唯一
server-id=1
#binlog-do-db可以被從服務器復制的庫
binlog-do-db=hotdata
#binlog-ignore-db不可以被從服務器復制的庫
binlog-ignore-db=mysql
[root@node7 ~]# systemctl restart mysqld #重啟MySQL資料庫
6.3.1.3 主庫給從庫授予replication權限
[root@node7 ~]# mysql -uroot -p123456
#授予node8從庫replication權限
mysql> grant replication slave on *.* to [email protected] identified by "123456";
Query OK, 0 rows affected, 1 warning (0.11 sec)
#授予node9從庫replication權限
mysql> grant replication slave on *.* to [email protected] identified by "123456";
Query OK, 0 rows affected, 1 warning (0.01 sec)
#重繪權限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#查看master狀態資訊
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000012 | 902 | hotdata | mysql | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> exit
Bye
#查看二進制日志
[root@node7 ~]# ll /data/binlogs/
total 2896
-rw-r----- 1 mysql mysql 177 Dec 3 11:47 mysql-bin.000001
-rw-r----- 1 mysql mysql 2915818 Dec 3 16:38 mysql-bin.000002
-rw-r----- 1 mysql mysql 201 Dec 3 16:38 mysql-bin.000003
-rw-r----- 1 mysql mysql 177 Dec 3 17:09 mysql-bin.000004
-rw-r----- 1 mysql mysql 177 Dec 3 17:14 mysql-bin.000005
-rw-r----- 1 mysql mysql 177 Dec 3 17:25 mysql-bin.000006
-rw-r----- 1 mysql mysql 1220 Dec 4 03:12 mysql-bin.000007
-rw-r----- 1 mysql mysql 201 Dec 4 03:12 mysql-bin.000008
-rw-r----- 1 mysql mysql 177 Dec 4 10:49 mysql-bin.000009
-rw-r----- 1 mysql mysql 1743 Dec 5 10:35 mysql-bin.000010
-rw-r----- 1 mysql mysql 665 Dec 5 10:59 mysql-bin.000011
-rw-r----- 1 mysql mysql 902 Dec 5 11:47 mysql-bin.000012
-rw-r----- 1 mysql mysql 372 Dec 5 11:40 mysql-bin.index
[root@node7 ~]# mysql -uroot -p123456
##查看二進制日志事件
mysql> show binlog events\G
*************************** 1. row ***************************
Log_name: mysql-bin.000001
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 123
Info: Server ver: 5.7.26-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mysql-bin.000001
Pos: 123
Event_type: Previous_gtids
Server_id: 1
End_log_pos: 154
Info:
*************************** 3. row ***************************
Log_name: mysql-bin.000001
Pos: 154
Event_type: Stop
Server_id: 1
End_log_pos: 177
Info:
3 rows in set (0.00 sec)
mysql> exit
Bye
6.3.1.4 備份主庫需要從庫同步的資料庫hotdata
#備份資料庫hotdata
[root@node7 ~]# mysqldump -uroot -p123456 hotdata >hotdata.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
#給從庫分發備份好的資料庫
[root@node7 ~]# scp hotdata.sql [email protected]:~/
hotdata.sql 100% 2239 510.9KB/s 00:00
[root@node7 ~]# scp hotdata.sql [email protected]:~/
hotdata.sql 100% 2239 382.8KB/s 00:00
6.3.2 配置從資料庫服務器node8
6.3.2.1 檢查資料庫版本
#主從資料庫版本不一致的話會出現問題
[root@node8 ~]# mysql -uroot -p123456
mysql> show variables like "%version%";
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.7.26 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1 |
| version | 5.7.26-log |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | linux-glibc2.12 |
+-------------------------+------------------------------+
8 rows in set (0.01 sec)
mysql> quit
Bye
6.3.2.2 測驗連接到主服務器是否成功
[root@node8 ~]# mysql -uslave -p123456 -h 192.168.110.188
#只有復制的權限, 是看不到其他庫的，
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
mysql> exit
Bye
6.3.2.3 匯入hotdata資料庫，和主資料庫保持一致
[root@node8 ~]# mysql -uroot -p123456
mysql> create database hotdata;
Query OK, 1 row affected (0.00 sec)
mysql> exit
Bye
#匯入hotdata表
[root@node8 ~]# mysql -uroot -p123456 hotdata<hotdata.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
6.3.2.4 修改組態檔
[root@node8 ~]# systemctl stop mysqld
[root@node8 ~]# vim /etc/my.cnf
[root@node8 ~]# cat /etc/my.cnf
[client]
port=3306
socket=/data/run/mysql.sock
[mysqld]
port=3306
socket=/data/run/mysql.sock
pid_file=/data/run/mysql.pid
datadir=/data/data
default_storage_engine=InnoDB
max_allowed_packet=512M
max_connections=2048
open_files_limit=65535
skip-name-resolve
lower_case_table_names=1
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
innodb_buffer_pool_size=1024M
innodb_log_file_size=2048M
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=0
key_buffer_size=64M
log-error=/data/log/mysql_error.log
slow_query_log=1
slow_query_log_file=/data/log/mysql_slow_query.log
long_query_time=5
tmp_table_size=32M
max_heap_table_size=32M
query_cache_type=0
query_cache_size=0
#mysql replication配置
#server-id必須唯一
server-id=2
#下面log-bin，binlog-do-db，binlog-ignore-db這三個引數都不是必須的
log-bin=/data/binlogs/mysql-bin
binlog-do-db=hotdata
binlog-ignore-db=mysql
[root@node8 ~]#
[root@node8 ~]# systemctl restart mysqld
6.3.2.5 從庫設定slave復制主庫資料
[root@node8 ~]# mysql -uroot -p123456
mysql> stop slave; #停止slave
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='192.168.110.188',master_user='slave',master_password='123456';
Query OK, 0 rows affected, 2 warnings (0.02 sec)
#釋義：
#change master to #master_host='192.168.0.68',master_user='root',master_password='root',master_log_file='#mysql-bin.000004', master_log_pos=28125;
#上面的master_log_file是在Master中show master status顯示的File，而master_log_pos是在Master中#show master status顯示的Position，
#也可以通過show slave status查看配置資訊,如果沒有同步成功，比對show slave status中的position和#file是否和show master status中的對應，
mysql> start slave; #啟動slave
Query OK, 0 rows affected (0.01 sec)
#查看slave狀態
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.110.188
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000012
Read_Master_Log_Pos: 902
Relay_Log_File: node8-relay-bin.000010
Relay_Log_Pos: 519
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Could not execute Update_rows event on table mysql.user; Duplicate entry '%-root' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000007, end_log_pos 942
Skip_Counter: 0
Exec_Master_Log_Pos: 306
Relay_Log_Space: 7216
Until_Condition: None
Until_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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Could not execute Update_rows event on table mysql.user; Duplicate entry '%-root' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000007, end_log_pos 942
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: a8da7421-157f-11ea-b1bf-000c297c0226
Master_Info_File: /data/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 191205 15:18:40
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
#可以看到Last_Error報錯了，是因為主鍵重復了，按照下面操作即可
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL sql_slave_skip_counter =1;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
#再次查看slave狀態，如果Last_Error沒報錯，并且Slave_IO_Running和Slave_SQL_Running都為yes則說明#配置成功了
#Slave_IO_Running ：負責與主機的IO通信
#Slave_SQL_Running：負責自己的slave mysql行程
#如果執行了stop slave，SET GLOBAL sql_slave_skip_counter =1，start slave之后，show slave #status\G還是報錯，則再次執行一遍stop slave，SET GLOBAL sql_slave_skip_counter =1，start #slave即可，最多執行3遍，即可消除所有錯誤，
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.110.188
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000013
Read_Master_Log_Pos: 154
Relay_Log_File: node8-relay-bin.000037
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000013
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: 154
Relay_Log_Space: 693
Until_Condition: None
Until_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: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: a8da7421-157f-11ea-b1bf-000c297c0226
Master_Info_File: /data/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
#查看資料目錄，可以發現Relay_Log_File
[root@node8 ~]# ls /data/data/
auto.cnf ibdata1 ibtmp1 node8-relay-bin.000036 performance_schema
hotdata ib_logfile0 master.info node8-relay-bin.000037 relay-log.info
ib_buffer_pool ib_logfile1 mysql node8-relay-bin.index sys
6.3.3 配置從資料庫服務器node9
? node9的配置和node8一樣，要注意的是組態檔my.cnf里server-id必須唯一，不能和node7,node8相同，
6.3.4 在主服務器上查看狀態
[root@node7 ~]# mysql -uroot -p123456
#可以看到有兩個slave
mysql> show processlist\G
*************************** 1. row ***************************
Id: 8
User: slave
Host: 192.168.110.186:49414
db: NULL
Command: Binlog Dump
Time: 4313
State: Master has sent all binlog to slave; waiting for more updates
Info: NULL
*************************** 2. row ***************************
Id: 10
User: slave
Host: 192.168.110.187:33510
db: NULL
Command: Binlog Dump
Time: 4208
State: Master has sent all binlog to slave; waiting for more updates
Info: NULL
*************************** 3. row ***************************
Id: 11
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: starting
Info: show processlist
3 rows in set (0.00 sec)
6.3.5 插入資料測驗主從同步
#在主服務器上插入資料
mysql> use hotdata;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> insert into customers values (1,'張三','珠江新城','廣州','廣東省','[email protected]','china');
Query OK, 1 row affected (0.10 sec)
mysql> insert into customers values (2,'李四','天安門','北京','北京市','[email protected]','china');
Query OK, 1 row affected (0.04 sec)
mysql> insert into customers values (3,'王二麻子','鐘鼓樓','昆明','云南省','[email protected]','china');
Query OK, 1 row affected (0.01 sec)
mysql> insert into customers values (4,'趙四','百花廣場','佛山','廣東省','[email protected]','china');
Query OK, 1 row affected (0.00 sec)
mysql> insert into customers values (5,'劉能','體育中心','廣州','廣東省','[email protected]','china');
Query OK, 1 row affected (0.00 sec)
mysql> insert into customers values (6,'謝廣坤','體育西路','廣州','廣東省','[email protected]','china');
Query OK, 1 row affected (0.00 sec)
mysql> select * from customers; #查看資料
+---------+--------------+--------------+-----------+------------+-------------------+--------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_email | cust_country |
+---------+--------------+--------------+-----------+------------+-------------------+--------------+
| 1 | 張三 | 珠江新城 | 廣州 | 廣東省 | [email protected] | china |
| 2 | 李四 | 天安門 | 北京 | 北京市 | [email protected] | china |
| 3 | 王二麻子 | 鐘鼓樓 | 昆明 | 云南省 | [email protected] | china |
| 4 | 趙四 | 百花廣場 | 佛山 | 廣東省 | [email protected] | china |
| 5 | 劉能 | 體育中心 | 廣州 | 廣東省 | [email protected] | china |
| 6 | 謝廣坤 | 體育西路 | 廣州 | 廣東省 | [email protected] | china |
+---------+--------------+--------------+-----------+------------+-------------------+--------------+
6 rows in set (0.01 sec)
mysql> exit
Bye
#在兩個slave上查看資料
[root@node8 ~]# mysql -uroot -p123456
#在node8上查看資料，發現資料已經同步
mysql> select * from hotdata.customers;
+---------+--------------+--------------+-----------+------------+-------------------+--------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_email | cust_country |
+---------+--------------+--------------+-----------+------------+-------------------+--------------+
| 1 | 張三 | 珠江新城 | 廣州 | 廣東省 | [email protected] | china |
| 2 | 李四 | 天安門 | 北京 | 北京市 | [email protected] | china |
| 3 | 王二麻子 | 鐘鼓樓 | 昆明 | 云南省 | [email protected] | china |
| 4 | 趙四 | 百花廣場 | 佛山 | 廣東省 | [email protected] | china |
| 5 | 劉能 | 體育中心 | 廣州 | 廣東省 | [email protected] | china |
| 6 | 謝廣坤 | 體育西路 | 廣州 | 廣東省 | [email protected] | china |
+---------+--------------+--------------+-----------+------------+-------------------+--------------+
6 rows in set (0.00 sec)
mysql> exit
Bye
[root@node9 ~]# mysql -uroot -p123456
#在node9上查看資料，發現資料已經同步
mysql> select * from hotdata.customers;
+---------+--------------+--------------+-----------+------------+-------------------+--------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_email | cust_country |
+---------+--------------+--------------+-----------+------------+-------------------+--------------+
| 1 | 張三 | 珠江新城 | 廣州 | 廣東省 | [email protected] | china |
| 2 | 李四 | 天安門 | 北京 | 北京市 | [email protected] | china |
| 3 | 王二麻子 | 鐘鼓樓 | 昆明 | 云南省 | [email protected] | china |
| 4 | 趙四 | 百花廣場 | 佛山 | 廣東省 | [email protected] | china |
| 5 | 劉能 | 體育中心 | 廣州 | 廣東省 | [email protected] | china |
| 6 | 謝廣坤 | 體育西路 | 廣州 | 廣東省 | [email protected] | china |
+---------+--------------+--------------+-----------+------------+-------------------+--------------+
6 rows in set (0.00 sec)
mysql> exit
Bye
注意：
- 主從同步，主資料庫上添加資料，從資料庫上同步，但是從資料庫添加資料，主不同步
- 由于是主從同步，如果主上洗掉了資料，那么從上的資料也就沒了，因此建議在主上做定期備份（mysqldump）
自此，MySQL主從同步之一主多從架構已經搭建完畢，
6.3.6 徹底取消主從同步
? 既然有搭建主從同步就有撤銷主從同步，如果有撤銷主從同步的需求，請看下文，
#在主庫上執行
#重置主記錄資訊
mysql> reset master;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 154
Binlog_Do_DB: hotdata
Binlog_Ignore_DB: mysql
Executed_Gtid_Set:
1 row in set (0.00 sec)
#在兩個從庫上執行
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
#清空從所有連接、資訊記錄
mysql> reset slave all;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
Empty set (0.00 sec)
? 可見主庫和從庫都已經解除了主從關系，最后把組態檔中與主從相關的配置洗掉即可，
6.4 總結
? MySQL主從同步之一主多從架構，一般用來做讀寫分離的，master負責寫入資料，其他slave負責讀取資料，這種架構最大問題I/O壓力集中,在Master上多臺同步影響IO
七.MySQL主從同步之主主雙向同步架構
7.1 服務器規劃
| 主機名 | IP | 作業系統版本 | MySQL版本 | 角色 |
|---|---|---|---|---|
| node7 | 192.168.110.188 | CentOS 7.4.1708 | 5.7.26 | master，slave（既是主也是從） |
| node8 | 192.168.110.186 | CentOS 7.4.1708 | 5.7.26 | master，slave（既是主也是從） |
7.2 主從同步的原理
? master將改變記錄到二進制日志(binary log)中,slave將master的binary log events拷貝到它的中繼日志(relay log),slave重做中繼日志中的事件，修改salve上的資料，
7.3 部署MySQL主從同步之主主雙向同步
7.3.1 配置資料庫服務器node7
? node7有雙重身份，既是node8的主，也是node8的從，
7.3.1.1 創建需要同步的資料庫及其表
[root@node7 ~]# mysql -uroot -p123456
mysql> create database hotdata; #創建熱點資料庫
Query OK, 1 row affected (0.70 sec)
mysql> use hotdata;
Database changed
#創建顧客表
mysql> create table customers(cust_id int,cust_name varchar(30),cust_address varchar(50),cust_city varchar(30),cust_state varchar(50),cust_email varchar(30),cust_country varchar(50));
Query OK, 0 rows affected (0.44 sec)
mysql> desc customers; #查看表結構
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| cust_id | int(11) | YES | | NULL | |
| cust_name | varchar(30) | YES | | NULL | |
| cust_address | varchar(50) | YES | | NULL | |
| cust_city | varchar(30) | YES | | NULL | |
| cust_state | varchar(50) | YES | | NULL | |
| cust_email | varchar(30) | YES | | NULL | |
| cust_country | varchar(50) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
7 rows in set (0.36 sec)
mysql> exit
Bye
7.3.1.2 修改MySQL組態檔
[root@node7 ~]# vim /etc/my.cnf
#與主從同步相關的配置在#mysql replication下面
[root@node7 ~]# cat /etc/my.cnf
[client]
port=3306
socket=/data/run/mysql.sock
[mysqld]
port=3306
socket=/data/run/mysql.sock
pid_file=/data/run/mysql.pid
datadir=/data/data
default_storage_engine=InnoDB
max_allowed_packet=512M
max_connections=2048
open_files_limit=65535
skip-name-resolve
lower_case_table_names=1
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
innodb_buffer_pool_size=1024M
innodb_log_file_size=2048M
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=0
key_buffer_size=64M
log-error=/data/log/mysql_error.log
slow_query_log=1
slow_query_log_file=/data/log/mysql_slow_query.log
long_query_time=5
tmp_table_size=32M
max_heap_table_size=32M
query_cache_type=0
query_cache_size=0
skip_ssl
#mysql replication
#logbin引數啟用二進制日志，并把二進制日志放在/data/binlogs目錄下
log-bin=/data/binlogs/mysql-bin
#資料庫標志ID，唯一
server-id=1
#binlog-do-db可以被從服務器復制的庫
binlog-do-db=hotdata
#binlog-ignore-db不可以被從服務器復制的庫
binlog-ignore-db=mysql
7.3.1.3 給node8授予replication的權限
#重啟MySQL
[root@node7 ~]# systemctl restart mysqld
[root@node7 ~]# mysql -uroot -p123456
#查看master狀態
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | hotdata | mysql | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
mysql> grant replication slave on *.* to slave@'192.168.110.186' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
#重繪權限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
7.3.2 配置資料庫服務器node8
? node8有雙重身份，既是node7的主，也是node7的從，
7.3.2.1 創建需要同步的資料庫及其表
[root@node8 ~]# mysql -uroot -p123456
mysql> create database hotdata; #創建熱點資料庫
Query OK, 1 row affected (0.70 sec)
mysql> use hotdata;
Database changed
#創建顧客表
mysql> create table customers(cust_id int,cust_name varchar(30),cust_address varchar(50),cust_city varchar(30),cust_state varchar(50),cust_email varchar(30),cust_country varchar(50));
Query OK, 0 rows affected (0.44 sec)
mysql> desc customers; #查看表結構
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| cust_id | int(11) | YES | | NULL | |
| cust_name | varchar(30) | YES | | NULL | |
| cust_address | varchar(50) | YES | | NULL | |
| cust_city | varchar(30) | YES | | NULL | |
| cust_state | varchar(50) | YES | | NULL | |
| cust_email | varchar(30) | YES | | NULL | |
| cust_country | varchar(50) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
7 rows in set (0.36 sec)
mysql> exit
Bye
7.3.2.2 修改MySQL組態檔
[root@node8 ~]# vim /etc/my.cnf
#主從同步相關的配置在#mysql replication配置下面
[root@node8 ~]# cat /etc/my.cnf
[client]
port=3306
socket=/data/run/mysql.sock
[mysqld]
port=3306
socket=/data/run/mysql.sock
pid_file=/data/run/mysql.pid
datadir=/data/data
default_storage_engine=InnoDB
max_allowed_packet=512M
max_connections=2048
open_files_limit=65535
skip-name-resolve
lower_case_table_names=1
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
innodb_buffer_pool_size=1024M
innodb_log_file_size=2048M
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=0
key_buffer_size=64M
log-error=/data/log/mysql_error.log
slow_query_log=1
slow_query_log_file=/data/log/mysql_slow_query.log
long_query_time=5
tmp_table_size=32M
max_heap_table_size=32M
query_cache_type=0
query_cache_size=0
#mysql replication配置
log-bin=/data/binlogs/mysql-bin
server-id=2
binlog-do-db=hotdata
binlog-ignore-db=mysql
[root@node8 ~]#
重啟MySQL
[root@node8 ~]# systemctl restart mysqld
7.3.2.3 測驗從賬號slave能否登陸node7
[root@node8 ~]# mysql -uslave -p123456 -h 192.168.110.188
#可以看到成功登陸node7
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
mysql> exit
Bye
7.3.2.4 給node7賦予replication權限，并設定node8復制node7
[root@node8 ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to slave@'192.168.110.188' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='192.168.110.188',master_user='slave',master_password='123456';
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.36 sec)
#查看slave狀態，如果Slave_IO_Running，Slave_SQL_Running都為yes，就表示ok
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.110.188
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 604
Relay_Log_File: node8-relay-bin.000002
Relay_Log_Pos: 817
Relay_Master_Log_File: mysql-bin.000001
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: 604
Relay_Log_Space: 1024
Until_Condition: None
Until_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: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: a8da7421-157f-11ea-b1bf-000c297c0226
Master_Info_File: /data/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
mysql> exit
Bye
7.3.3 配置資料庫服務器node7
7.3.3.1 測驗從賬號slave能否登陸node8
[root@node7 ~]# mysql -uslave -p123456 -h 192.168.110.186
#可以看到成功使用slave賬號登錄node8
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
mysql> exit
Bye
7.3.3.2 設定node7復制node8
[root@node7 ~]# mysql -uroot -p123456
mysql> change master to master_host='192.168.110.186',master_user='slave',master_password='123456';
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.10 sec)
#查看slave狀態，如果Slave_IO_Running，Slave_SQL_Running都為yes，就表示ok
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.110.186
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 604
Relay_Log_File: node7-relay-bin.000003
Relay_Log_Pos: 817
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: 604
Relay_Log_Space: 1391
Until_Condition: None
Until_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: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: f083c41e-1671-11ea-8342-000c29f7e789
Master_Info_File: /data/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.01 sec)
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 604
Binlog_Do_DB: hotdata
Binlog_Ignore_DB: mysql
Executed_Gtid_Set:
1 row in set (0.00 sec)
7.3.4 插入資料測驗主從是否同步
7.3.4.1 在node7上插入資料，查看node8 上有沒有同步資料
#node7上插入資料
mysql> insert into hotdata.customers values (1,'張三','珠江新城','廣州','廣東省','[email protected]','china');
Query OK, 1 row affected (0.01 sec)
mysql> insert into hotdata.customers values (2,'李四','天安門','北京','北京市','[email protected]','china');
Query OK, 1 row affected (0.00 sec)
mysql> insert into hotdata.customers values (3,'王二麻子','鐘鼓樓','昆明','云南省','[email protected]','china');
Query OK, 1 row affected (0.01 sec)
mysql> insert into hotdata.customers values (4,'趙四','百花廣場','佛山','廣東省','[email protected]','china');
Query OK, 1 row affected (0.01 sec)
mysql> insert into hotdata.customers values (2,'李四','天安門','北京','北京市','[email protected]','china');
values (3,'王二麻子','鐘鼓樓','昆明','云南省','[email protected]','china');
insert into hotdata.customers values (4,'趙四','百花廣場','佛山','廣東省','[email protected]','china');Query OK, 1 row affected (0.00 sec)
mysql> insert into hotdata.customers values (3,'王二麻子','鐘鼓樓','昆明','云南省','[email protected]','china');
Query OK, 1 row affected (0.00 sec)
mysql> insert into hotdata.customers values (4,'趙四','百花廣場','佛山','廣東省','[email protected]','china');
Query OK, 1 row affected (0.00 sec)
mysql> select * from hotdata.customers;
+---------+--------------+--------------+-----------+------------+-------------------+--------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_email | cust_country |
+---------+--------------+--------------+-----------+------------+-------------------+--------------+
| 1 | 張三 | 珠江新城 | 廣州 | 廣東省 | [email protected] | china |
| 2 | 李四 | 天安門 | 北京 | 北京市 | [email protected] | china |
| 3 | 王二麻子 | 鐘鼓樓 | 昆明 | 云南省 | [email protected] | china |
| 4 | 趙四 | 百花廣場 | 佛山 | 廣東省 | [email protected] | china |
| 2 | 李四 | 天安門 | 北京 | 北京市 | [email protected] | china |
| 3 | 王二麻子 | 鐘鼓樓 | 昆明 | 云南省 | [email protected] | china |
| 4 | 趙四 | 百花廣場 | 佛山 | 廣東省 | [email protected] | china |
+---------+--------------+--------------+-----------+------------+-------------------+--------------+
7 rows in set (0.00 sec)
#在node8上查詢資料
mysql> select * from hotdata.customers;
+---------+--------------+--------------+-----------+------------+-------------------+--------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_email | cust_country |
+---------+--------------+--------------+-----------+------------+-------------------+--------------+
| 1 | 張三 | 珠江新城 | 廣州 | 廣東省 | [email protected] | china |
| 2 | 李四 | 天安門 | 北京 | 北京市 | [email protected] | china |
| 3 | 王二麻子 | 鐘鼓樓 | 昆明 | 云南省 | [email protected] | china |
| 4 | 趙四 | 百花廣場 | 佛山 | 廣東省 | [email protected] | china |
| 2 | 李四 | 天安門 | 北京 | 北京市 | [email protected] | china |
| 3 | 王二麻子 | 鐘鼓樓 | 昆明 | 云南省 | [email protected] | china |
| 4 | 趙四 | 百花廣場 | 佛山 | 廣東省 | [email protected] | china |
+---------+--------------+--------------+-----------+------------+-------------------+--------------+
7 rows in set (0.00 sec)
7.3.4.2 在node8上插入資料，查看node7 上有沒有同步資料
#node8上插入資料
mysql> insert into hotdata.customers values (5,'劉能','體育中心','廣州','廣東省','[email protected]','china');
tomers values (7,'人生的哲理','塔坡山','大理','云南省','[email protected]','china');
insert into hotdata.customers values (8,'美劇','美劇','美國','美國','[email protected]','usa');Query OK, 1 row affected (0.01 sec)
mysql> insert into hotdata.customers values (6,'謝廣坤','體育西路','廣州','廣東省','[email protected]','china');
Query OK, 1 row affected (0.00 sec)
mysql> insert into hotdata.customers values (7,'人生的哲理','塔坡山','大理','云南省','[email protected]','china');
Query OK, 1 row affected (0.00 sec)
mysql> insert into hotdata.customers values (8,'美劇','美劇','美國','美國','[email protected]','usa');
Query OK, 1 row affected (0.00 sec)
mysql> select * from hotdata.customers;
+---------+-----------------+--------------+-----------+------------+-------------------+--------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_email | cust_country |
+---------+-----------------+--------------+-----------+------------+-------------------+--------------+
| 1 | 張三 | 珠江新城 | 廣州 | 廣東省 | [email protected] | china |
| 2 | 李四 | 天安門 | 北京 | 北京市 | [email protected] | china |
| 3 | 王二麻子 | 鐘鼓樓 | 昆明 | 云南省 | [email protected] | china |
| 4 | 趙四 | 百花廣場 | 佛山 | 廣東省 | [email protected] | china |
| 2 | 李四 | 天安門 | 北京 | 北京市 | [email protected] | china |
| 3 | 王二麻子 | 鐘鼓樓 | 昆明 | 云南省 | [email protected] | china |
| 4 | 趙四 | 百花廣場 | 佛山 | 廣東省 | [email protected] | china |
| 5 | 劉能 | 體育中心 | 廣州 | 廣東省 | [email protected] | china |
| 6 | 謝廣坤 | 體育西路 | 廣州 | 廣東省 | [email protected] | china |
| 7 | 人生的哲理 | 塔坡山 | 大理 | 云南省 | [email protected] | china |
| 8 | 美劇 | 美劇 | 美國 | 美國 | [email protected] | usa |
+---------+-----------------+--------------+-----------+------------+-------------------+--------------+
11 rows in set (0.00 sec)
#node7查詢資料
mysql> select * from hotdata.customers;
+---------+-----------------+--------------+-----------+------------+-------------------+--------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_email | cust_country |
+---------+-----------------+--------------+-----------+------------+-------------------+--------------+
| 1 | 張三 | 珠江新城 | 廣州 | 廣東省 | [email protected] | china |
| 2 | 李四 | 天安門 | 北京 | 北京市 | [email protected] | china |
| 3 | 王二麻子 | 鐘鼓樓 | 昆明 | 云南省 | [email protected] | china |
| 4 | 趙四 | 百花廣場 | 佛山 | 廣東省 | [email protected] | china |
| 2 | 李四 | 天安門 | 北京 | 北京市 | [email protected] | china |
| 3 | 王二麻子 | 鐘鼓樓 | 昆明 | 云南省 | [email protected] | china |
| 4 | 趙四 | 百花廣場 | 佛山 | 廣東省 | [email protected] | china |
| 5 | 劉能 | 體育中心 | 廣州 | 廣東省 | [email protected] | china |
| 6 | 謝廣坤 | 體育西路 | 廣州 | 廣東省 | [email protected] | china |
| 7 | 人生的哲理 | 塔坡山 | 大理 | 云南省 | [email protected] | china |
| 8 | 美劇 | 美劇 | 美國 | 美國 | [email protected] | usa |
+---------+-----------------+--------------+-----------+------------+-------------------+--------------+
11 rows in set (0.01 sec)
由此可就主主雙向同步搭建完畢，
7.3.5 徹底取消主從同步
既然有搭建主從同步就有撤銷主從同步，如果有撤銷主從同步的需求，請看下文，
由于是主主雙向同步，所以兩個mysql服務器都要清除matser和slave的配置，
#在兩個MySQL上都執行如下操作，以node7為例
#清除master配置
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 154
Binlog_Do_DB: hotdata
Binlog_Ignore_DB: mysql
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql> stop slave;
Query OK, 0 rows affected (0.88 sec)
mysql> reset slave all;
Query OK, 0 rows affected (0.12 sec)
#清除slave配置
mysql> show slave status\G
Empty set (0.00 sec)
可見主庫和從庫都已經解除了主從關系，最后把組態檔中與主從相關的配置洗掉即可，
7.4 總結
? 對于MySQL主從同步之主主雙向同步架構，很多人誤以為這樣可以做到MySQL負載均衡，實際上非常不好，每個服務器需要做同樣的同步更新，破壞了事物的隔離性和資料的一致性，不推薦，
八.MySQL主從同步之M-S-S架構
8.1 服務器規劃
| 主機名 | IP | 作業系統版本 | MySQL版本 | 角色 |
|---|---|---|---|---|
| node7 | 192.168.110.188 | CentOS 7.4.1708 | 5.7.26 | master（主） |
| node8 | 192.168.110.186 | CentOS 7.4.1708 | 5.7.26 | slave中繼（中繼） |
| node9 | 192.168.110.187 | CentOS 7.4.1708 | 5.7.26 | slave（從） |
8.2 主從同步的原理
? master將改變記錄到二進制日志(binary log)中,slave將master的binary log events拷貝到它的中繼日志(relay log),slave重做中繼日志中的事件，修改salve上的資料，
? 由于一主多從的結構IO壓力集中在master上，所以使用一臺slave作為中繼，分擔Master的壓力，slave中繼需要開啟bin-log,并配置log-slave-updates，Slave中繼可使用Black-hole存盤引擎，不會把資料存盤到磁盤，只記錄二進制日志，
8.3 部署MySQL主從同步之M-S-S
8.3.1 配置主資料庫服務器node7
8.3.1.1 創建需要同步的資料庫及其表
[root@node7 ~]# mysql -uroot -p123456
mysql> create database hotdata; #創建熱點資料庫
Query OK, 1 row affected (0.70 sec)
mysql> use hotdata;
Database changed
#創建顧客表
mysql> create table customers(cust_id int,cust_name varchar(30),cust_address varchar(50),cust_city varchar(30),cust_state varchar(50),cust_email varchar(30),cust_country varchar(50));
Query OK, 0 rows affected (0.44 sec)
mysql> desc customers; #查看表結構
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| cust_id | int(11) | YES | | NULL | |
| cust_name | varchar(30) | YES | | NULL | |
| cust_address | varchar(50) | YES | | NULL | |
| cust_city | varchar(30) | YES | | NULL | |
| cust_state | varchar(50) | YES | | NULL | |
| cust_email | varchar(30) | YES | | NULL | |
| cust_country | varchar(50) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
7 rows in set (0.36 sec)
mysql> exit
Bye
8.3.1.2 授予node8 replication的權限
[root@node7 ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to repl@'192.168.110.186' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.36 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.14 sec)
mysql> exit
Bye
8.3.1.3 修改組態檔并重啟
[root@node7 ~]# vim /etc/my.cnf
#與主從同步相關的配置在#mysql replication下面
[root@node7 ~]# cat /etc/my.cnf
[client]
port=3306
socket=/data/run/mysql.sock
[mysqld]
port=3306
socket=/data/run/mysql.sock
pid_file=/data/run/mysql.pid
datadir=/data/data
default_storage_engine=InnoDB
max_allowed_packet=512M
max_connections=2048
open_files_limit=65535
skip-name-resolve
lower_case_table_names=1
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
innodb_buffer_pool_size=1024M
innodb_log_file_size=2048M
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=0
key_buffer_size=64M
log-error=/data/log/mysql_error.log
slow_query_log=1
slow_query_log_file=/data/log/mysql_slow_query.log
long_query_time=5
tmp_table_size=32M
max_heap_table_size=32M
query_cache_type=0
query_cache_size=0
skip_ssl
#mysql replication
log-bin=/data/binlogs/mysql-bin
#server-id=1必須唯一
server-id=1
binlog-do-db=hotdata
binlog-ignore-db=mysql
#當每進行1次事務提交之后，MySQL將進行一次fsync之類的磁盤同步指令來將binlog_cache中的資料強制寫入磁盤
#sync-binlog具體解釋請看：https://www.cnblogs.com/wt645631686/p/8109002.html
sync-binlog=1
#ROW模式（RBR）:不記錄每條sql陳述句的背景關系資訊，僅需記錄哪條資料被修改了，修改成什么樣了
#具體解釋查看：https://www.cnblogs.com/xingyunfashi/p/8431780.html
binlog-format=row
[root@node7 ~]# systemctl restart mysqld
8.3.2 配置slave中繼node8
8.3.2.1 創建需要同步的資料庫及其表
[root@node8 ~]# mysql -uroot -p123456
mysql> create database hotdata; #創建熱點資料庫
Query OK, 1 row affected (0.70 sec)
mysql> use hotdata;
Database changed
#創建顧客表
mysql> create table customers(cust_id int,cust_name varchar(30),cust_address varchar(50),cust_city varchar(30),cust_state varchar(50),cust_email varchar(30),cust_country varchar(50));
Query OK, 0 rows affected (0.44 sec)
mysql> desc customers; #查看表結構
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| cust_id | int(11) | YES | | NULL | |
| cust_name | varchar(30) | YES | | NULL | |
| cust_address | varchar(50) | YES | | NULL | |
| cust_city | varchar(30) | YES | | NULL | |
| cust_state | varchar(50) | YES | | NULL | |
| cust_email | varchar(30) | YES | | NULL | |
| cust_country | varchar(50) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
7 rows in set (0.36 sec)
mysql> exit
Bye
8.3.2.2 修改組態檔并重啟
##與主從同步相關的配置在#mysql replication下面
[root@node8 ~]# cat /etc/my.cnf
[client]
port=3306
socket=/data/run/mysql.sock
[mysqld]
port=3306
socket=/data/run/mysql.sock
pid_file=/data/run/mysql.pid
datadir=/data/data
default_storage_engine=InnoDB
max_allowed_packet=512M
max_connections=2048
open_files_limit=65535
skip-name-resolve
lower_case_table_names=1
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
innodb_buffer_pool_size=1024M
innodb_log_file_size=2048M
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=0
key_buffer_size=64M
log-error=/data/log/mysql_error.log
slow_query_log=1
slow_query_log_file=/data/log/mysql_slow_query.log
long_query_time=5
tmp_table_size=32M
max_heap_table_size=32M
query_cache_type=0
query_cache_size=0
#mysql replication配置
log-bin=/data/binlogs/mysql-bin
server-id=2
#log-slave-updates引數默認是關閉的狀態，如果不手動設定，那么bin-log只會記錄直接在該庫上執行的SQL語##句，由replication機制的SQL執行緒讀取relay-log而執行的SQL陳述句并不會記錄到bin-log，那么就無法實作上#述的三級級聯同步，
log-slave-updates=1
binlog-format=row
relay-log=/data/data/relay-log.info
[root@node8 ~]#
[root@node8 ~]# systemctl restart mysqld
8.3.2.3 授權node8復制node7,并授予node9 replication的權限
[root@node8 ~]# mysql -uroot -p123456
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='192.168.110.188',master_user='repl',master_password='123456';
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.110.188
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: relay-log.000003
Relay_Log_Pos: 367
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: 154
Relay_Log_Space: 1230
Until_Condition: None
Until_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: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: a8da7421-157f-11ea-b1bf-000c297c0226
Master_Info_File: /data/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
mysql> grant replication slave on *.* to 'repl'@'192.168.110.187' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
8.3.3 配置從服務器node9
8.3.3.1 創建需要同步的資料庫及其表
[root@node9 ~]# mysql -uroot -p123456
mysql> create database hotdata; #創建熱點資料庫
Query OK, 1 row affected (0.70 sec)
mysql> use hotdata;
Database changed
#創建顧客表
mysql> create table customers(cust_id int,cust_name varchar(30),cust_address varchar(50),cust_city varchar(30),cust_state varchar(50),cust_email varchar(30),cust_country varchar(50));
Query OK, 0 rows affected (0.44 sec)
mysql> desc customers; #查看表結構
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| cust_id | int(11) | YES | | NULL | |
| cust_name | varchar(30) | YES | | NULL | |
| cust_address | varchar(50) | YES | | NULL | |
| cust_city | varchar(30) | YES | | NULL | |
| cust_state | varchar(50) | YES | | NULL | |
| cust_email | varchar(30) | YES | | NULL | |
| cust_country | varchar(50) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
7 rows in set (0.36 sec)
mysql> exit
Bye
8.3.3.2 修改組態檔并重啟
[root@node9 ~]# vim /etc/my.cnf
[root@node9 ~]# cat /etc/my.cnf
[client]
port=3306
socket=/data/run/mysql.sock
[mysqld]
port=3306
socket=/data/run/mysql.sock
pid_file=/data/run/mysql.pid
datadir=/data/data
default_storage_engine=InnoDB
max_allowed_packet=512M
max_connections=2048
open_files_limit=65535
skip-name-resolve
lower_case_table_names=1
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
innodb_buffer_pool_size=1024M
innodb_log_file_size=2048M
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=0
key_buffer_size=64M
log-error=/data/log/mysql_error.log
slow_query_log=1
slow_query_log_file=/data/log/mysql_slow_query.log
long_query_time=5
tmp_table_size=32M
max_heap_table_size=32M
query_cache_type=0
query_cache_size=0
#mysql replication配置
server-id=3
log-bin=/data/binlogs/mysql-bin
binlog-format=row
#relay-log=/data/relaylog/relay.log
relay-log=/data/data/relay-log.info
[root@node9 ~]#
[root@node9 ~]# systemctl restart mysqld
8.3.3.3 指定node8為node9的主
[root@node9 ~]# mysql -uroot -p123456
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='192.168.110.186',master_user='repl',master_password='123456';
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.110.186
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1052
Relay_Log_File: relay-log.000003
Relay_Log_Pos: 1265
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: 1052
Relay_Log_Space: 1679
Until_Condition: None
Until_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: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: f083c41e-1671-11ea-8342-000c29f7e789
Master_Info_File: /data/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.01 sec)
mysql> exit
Bye
8.3.4 插入資料測驗
#在node7上插入資料，查看node8和node9是否同步了資料
#先在node7上插入資料
[root@node7 ~]# mysql -uroot -p123456
mysql> insert into hotdata.customers values (1,'張三','珠江新城','廣州','廣東省','[email protected]','china');
mysql> insert into hotdata.customers values (2,'李四','天安門','北京','北京市','[email protected]','china');
Query OK, 1 row affected (0.01 sec)
mysql> insert into hotdata.customers values (3,'王二麻子','鐘鼓樓','昆明','云南省','[email protected]','china');
','云南省','[email protected]','china');Query OK, 1 row affected (0.09 sec)
mysql> insert into hotdata.customers values (4,'趙四','百花廣場','佛山','廣東省','[email protected]','china');
Query OK, 1 row affected (0.00 sec)
mysql> insert into hotdata.customers values (5,'劉能','體育中心','廣州','廣東省','[email protected]','china');
Query OK, 1 row affected (0.02 sec)
mysql> insert into hotdata.customers values (6,'謝廣坤','體育西路','廣州','廣東省','[email protected]','china');
Query OK, 1 row affected (0.01 sec)
mysql> insert into hotdata.customers values (7,'人生的哲理','塔坡山','大理','云南省','[email protected]','china');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql>
mysql> select * from hotdata.customers;
+---------+-----------------+--------------+-----------+------------+-------------------+--------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_email | cust_country |
+---------+-----------------+--------------+-----------+------------+-------------------+--------------+
| 1 | 張三 | 珠江新城 | 廣州 | 廣東省 | [email protected] | china |
| 2 | 李四 | 天安門 | 北京 | 北京市 | [email protected] | china |
| 3 | 王二麻子 | 鐘鼓樓 | 昆明 | 云南省 | [email protected] | china |
| 4 | 趙四 | 百花廣場 | 佛山 | 廣東省 | [email protected] | china |
| 5 | 劉能 | 體育中心 | 廣州 | 廣東省 | [email protected] | china |
| 6 | 謝廣坤 | 體育西路 | 廣州 | 廣東省 | [email protected] | china |
| 7 | 人生的哲理 | 塔坡山 | 大理 | 云南省 | [email protected] | china |
+---------+-----------------+--------------+-----------+------------+-------------------+--------------+
7 rows in set (0.00 sec)
#在node8上查看資料
[root@node8 ~]# mysql -uroot -p123456
mysql> select * from hotdata.customers;
+---------+-----------------+--------------+-----------+------------+-------------------+--------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_email | cust_country |
+---------+-----------------+--------------+-----------+------------+-------------------+--------------+
| 1 | 張三 | 珠江新城 | 廣州 | 廣東省 | [email protected] | china |
| 2 | 李四 | 天安門 | 北京 | 北京市 | [email protected] | china |
| 3 | 王二麻子 | 鐘鼓樓 | 昆明 | 云南省 | [email protected] | china |
| 4 | 趙四 | 百花廣場 | 佛山 | 廣東省 | [email protected] | china |
| 5 | 劉能 | 體育中心 | 廣州 | 廣東省 | [email protected] | china |
| 6 | 謝廣坤 | 體育西路 | 廣州 | 廣東省 | [email protected] | china |
| 7 | 人生的哲理 | 塔坡山 | 大理 | 云南省 | [email protected] | china |
+---------+-----------------+--------------+-----------+------------+-------------------+--------------+
7 rows in set (0.00 sec)
#在node9上查看資料
[root@node9 ~]# mysql -uroot -p123456
mysql> select * from hotdata.customers;
+---------+-----------------+--------------+-----------+------------+-------------------+--------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_email | cust_country |
+---------+-----------------+--------------+-----------+------------+-------------------+--------------+
| 1 | 張三 | 珠江新城 | 廣州 | 廣東省 | [email protected] | china |
| 2 | 李四 | 天安門 | 北京 | 北京市 | [email protected] | china |
| 3 | 王二麻子 | 鐘鼓樓 | 昆明 | 云南省 | [email protected] | china |
| 4 | 趙四 | 百花廣場 | 佛山 | 廣東省 | [email protected] | china |
| 5 | 劉能 | 體育中心 | 廣州 | 廣東省 | [email protected] | china |
| 6 | 謝廣坤 | 體育西路 | 廣州 | 廣東省 | [email protected] | china |
| 7 | 人生的哲理 | 塔坡山 | 大理 | 云南省 | [email protected] | china |
+---------+-----------------+--------------+-----------+------------+-------------------+--------------+
7 rows in set (0.00 sec)
可以發現現在資料都已經同步了，但是有一個問題，slave中繼node8也查到了資料，不符合需求，
設定node8需要同步的表hotdata.customers的存盤引擎為blackhole
#關閉日志記錄
mysql> set sql_log_bin=off;
Query OK, 0 rows affected (0.01 sec)
#Blackhole引擎–“黑洞”. 其作用正如其名字一樣：任何寫入到此引擎的資料均會被丟棄掉，不做實際存盤；#Select陳述句的內容永遠是空， 和Linux中的 /dev/null 檔案完成的作用完全一致，
mysql> alter table hotdata.customers ENGINE=blackhole;
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> select * from hotdata.customers;
Empty set (0.00 sec)
此時，node7上插入資料，node9同步資料，node8只分擔node7的IO壓力，不存盤資料，
自此，MySQL主從同步之M-S-S架構搭建完畢，
8.4 總結
? MySQL主從同步之M-S-S架構的好處是可以極大的減輕主節點的壓力，
? MySQL級聯復制的另一用途是進行資料遷移， 比如新上的兩臺服務器B和C，要替換掉之前舊的服務器A，同時B和C是新的主從關系，因此，配置成級聯復制，來遷移資料，也方便切換，
轉換流程如下：
master A ------> slave B ------> slave C =轉換為===> matser B ------> slave C
九.MySQL主從同步之多主多從架構
9.1 服務器規劃
| 主機名 | IP | 作業系統版本 | MySQL版本 | 角色 |
|---|---|---|---|---|
| node6 | 192.168.110.185 | CentOS 7.4.1708 | 5.7.26 | master,slave |
| node7 | 192.168.110.188 | CentOS 7.4.1708 | 5.7.26 | master,slave |
| node8 | 192.168.110.186 | CentOS 7.4.1708 | 5.7.26 | slave |
| node9 | 192.168.110.187 | CentOS 7.4.1708 | 5.7.26 | slave |
9.2 MySQL多主多從架構圖
? 架構圖說明：node6和node8，node7和node9為一主一從架構，node6和node7為主主雙向同步，
9.3 主從同步的原理
? master將改變記錄到二進制日志(binary log)中,slave將master的binary log events拷貝到它的中繼日志(relay log),slave重做中繼日志中的事件，修改salve上的資料，
? 一主多從架構可以緩解讀的壓力，但是一旦主資料庫宕機了，就不能寫了，使用雙主雙從架構的話，一個主資料庫宕機了，使用另一個主資料庫替代即可，
9.4 部署MySQL主從同步之雙主雙從
9.4.1 配置主資料庫服務器node6
9.4.1.1 創建需要同步的資料庫及其表
[root@node6 ~]# mysql -uroot -p123456
mysql> create database hotdata; #創建熱點資料庫
Query OK, 1 row affected (0.70 sec)
mysql> use hotdata;
Database changed
#創建顧客表
mysql> create table customers(cust_id int,cust_name varchar(30),cust_address varchar(50),cust_city varchar(30),cust_state varchar(50),cust_email varchar(30),cust_country varchar(50));
Query OK, 0 rows affected (0.44 sec)
mysql> desc customers; #查看表結構
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| cust_id | int(11) | YES | | NULL | |
| cust_name | varchar(30) | YES | | NULL | |
| cust_address | varchar(50) | YES | | NULL | |
| cust_city | varchar(30) | YES | | NULL | |
| cust_state | varchar(50) | YES | | NULL | |
| cust_email | varchar(30) | YES | | NULL | |
| cust_country | varchar(50) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
7 rows in set (0.36 sec)
mysql> exit
Bye
9.4.1.2 修改node6的MySQL組態檔
[root@node6 ~]# vim /etc/my.cnf
#MySQL主從同步的配置在#mysql replication下
[root@node6 ~]# cat /etc/my.cnf
[client]
port=3306
socket=/data/run/mysql.sock
default-character-set=utf8
[mysqld]
port=3306
socket=/data/run/mysql.sock
pid_file=/data/run/mysql.pid
datadir=/data/data
default_storage_engine=InnoDB
max_allowed_packet=512M
max_connections=2048
open_files_limit=65535
skip-name-resolve
lower_case_table_names=1
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
innodb_buffer_pool_size=1024M
innodb_log_file_size=2048M
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=0
key_buffer_size=64M
log-error=/data/log/mysql_error.log
slow_query_log=1
slow_query_log_file=/data/log/mysql_slow_query.log
long_query_time=5
tmp_table_size=32M
max_heap_table_size=32M
query_cache_type=0
query_cache_size=0
#mysql replication
#server-id必須唯一
server-id=4
#logbin引數啟用二進制日志，并把二進制日志放在/data/binlogs目錄下
log-bin=/data/binlogs/mysql-bin
#binlog-do-db可以被從服務器復制的庫
binlog-do-db=hotdata
#binlog-ignore-db不可以被從服務器復制的庫
binlog-ignore-db=mysql
##auto_increment_increment，控制主鍵自增的自增步長，用于防止Master與Master之間復制出現重復自增字##段值，通常auto_increment_increment=n，有多少臺主服務器，n 就設定為多少；
auto_increment_increment=2
#auto_increment_offset＝1設定自增起始值，這里設定為1，這樣Master的auto_increment欄位產生的數值##是：1, 3, 5, 7, …等奇數ID，注意auto_increment_offset的設定，不同的master設定不應該一樣，否則就##容易引起主鍵沖突，比如master1的offset=1，則master2的offset=2，master3的offset=3
auto_increment_offset=1
#在雙主模式中，log-slave-updates 配置項一定要配置，否則在node6上進行了更新資料，在#node7和node8上會更新，但是在node9上不會更新
log-slave-updates
#sync_binlog表示每幾次事務提交，MySQL把binlog快取刷進日志檔案中，默認是0，最安全的是設定為1；
sync_binlog=1
#重啟MySQL
[root@node6 ~]# systemctl restart mysqld
[root@node6 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Thu 2019-12-12 15:02:26 CST; 28s ago
Docs: man:mysqld(5.7)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 3753 ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Main PID: 3755 (mysqld)
CGroup: /system.slice/mysqld.service
└─3755 /usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid
Dec 12 15:02:25 node6 systemd[1]: Starting MySQL Server...
Dec 12 15:02:26 node6 systemd[1]: Started MySQL Server.
9.4.1.3 node6創建復制賬號并授權給node7和node8
[root@node6 ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to 'copy'@'192.168.110.188' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> grant replication slave on *.* to 'copy'@'192.168.110.186' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
9.4.2 配置主資料庫服務器node7
9.4.2.1 創建需要同步的資料庫及其表
[root@node7 ~]# mysql -uroot -p123456
mysql> create database hotdata; #創建熱點資料庫
Query OK, 1 row affected (0.70 sec)
mysql> use hotdata;
Database changed
#創建顧客表
mysql> create table customers(cust_id int,cust_name varchar(30),cust_address varchar(50),cust_city varchar(30),cust_state varchar(50),cust_email varchar(30),cust_country varchar(50));
Query OK, 0 rows affected (0.44 sec)
mysql> desc customers; #查看表結構
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| cust_id | int(11) | YES | | NULL | |
| cust_name | varchar(30) | YES | | NULL | |
| cust_address | varchar(50) | YES | | NULL | |
| cust_city | varchar(30) | YES | | NULL | |
| cust_state | varchar(50) | YES | | NULL | |
| cust_email | varchar(30) | YES | | NULL | |
| cust_country | varchar(50) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
7 rows in set (0.36 sec)
mysql> exit
Bye
9.4.2.2 修改node7的MySQL組態檔
[root@node7 ~]# vim /etc/my.cnf
[root@node7 ~]# cat /etc/my.cnf
[client]
port=3306
socket=/data/run/mysql.sock
default-character-set=utf8
[mysqld]
port=3306
socket=/data/run/mysql.sock
pid_file=/data/run/mysql.pid
datadir=/data/data
default_storage_engine=InnoDB
max_allowed_packet=512M
max_connections=2048
open_files_limit=65535
skip-name-resolve
lower_case_table_names=1
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
innodb_buffer_pool_size=1024M
innodb_log_file_size=2048M
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=0
key_buffer_size=64M
log-error=/data/log/mysql_error.log
slow_query_log=1
slow_query_log_file=/data/log/mysql_slow_query.log
long_query_time=5
tmp_table_size=32M
max_heap_table_size=32M
query_cache_type=0
query_cache_size=0
skip_ssl
#mysql replication
log-bin=/data/binlogs/mysql-bin
server-id=1
binlog-do-db=hotdata
binlog-ignore-db=mysql
auto_increment_increment=2
auto_increment_offset=2
log-slave-updates
sync_binlog=1
[root@node7 ~]# systemctl restart mysqld
[root@node7 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Thu 2019-12-12 15:02:29 CST; 26s ago
Docs: man:mysqld(5.7)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 14635 ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Main PID: 14637 (mysqld)
CGroup: /system.slice/mysqld.service
└─14637 /usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid
Dec 12 15:02:25 node7 systemd[1]: Starting MySQL Server...
Dec 12 15:02:29 node7 systemd[1]: Started MySQL Server.
9.4.2.3 node7創建復制賬號并授權給node6和node9
[root@node7 ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to 'copy'@'192.168.110.185' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.12 sec)
mysql> grant replication slave on *.* to 'copy'@'192.168.110.187' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
9.4.3 配置從資料庫服務器node8
9.4.3.1 創建需要同步的資料庫及其表
[root@node8 ~]# mysql -uroot -p123456
mysql> create database hotdata; #創建熱點資料庫
Query OK, 1 row affected (0.70 sec)
mysql> use hotdata;
Database changed
#創建顧客表
mysql> create table customers(cust_id int,cust_name varchar(30),cust_address varchar(50),cust_city varchar(30),cust_state varchar(50),cust_email varchar(30),cust_country varchar(50));
Query OK, 0 rows affected (0.44 sec)
mysql> desc customers; #查看表結構
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| cust_id | int(11) | YES | | NULL | |
| cust_name | varchar(30) | YES | | NULL | |
| cust_address | varchar(50) | YES | | NULL | |
| cust_city | varchar(30) | YES | | NULL | |
| cust_state | varchar(50) | YES | | NULL | |
| cust_email | varchar(30) | YES | | NULL | |
| cust_country | varchar(50) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
7 rows in set (0.36 sec)
mysql> exit
Bye
9.4.3.2 修改node8的MySQL組態檔
[root@node8 ~]# vim /etc/my.cnf
[root@node8 ~]# cat /etc/my.cnf
[client]
port=3306
socket=/data/run/mysql.sock
default-character-set=utf8
[mysqld]
port=3306
socket=/data/run/mysql.sock
pid_file=/data/run/mysql.pid
datadir=/data/data
default_storage_engine=InnoDB
max_allowed_packet=512M
max_connections=2048
open_files_limit=65535
skip-name-resolve
lower_case_table_names=1
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
innodb_buffer_pool_size=1024M
innodb_log_file_size=2048M
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=0
key_buffer_size=64M
log-error=/data/log/mysql_error.log
slow_query_log=1
slow_query_log_file=/data/log/mysql_slow_query.log
long_query_time=5
tmp_table_size=32M
max_heap_table_size=32M
query_cache_type=0
query_cache_size=0
#mysql replication配置
server-id=2
[root@node8 ~]# systemctl restart mysqld
[root@node8 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Thu 2019-12-12 15:02:28 CST; 27s ago
Docs: man:mysqld(5.7)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 24078 ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Main PID: 24080 (mysqld)
CGroup: /system.slice/mysqld.service
└─24080 /usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid
Dec 12 15:02:25 node8 systemd[1]: Starting MySQL Server...
Dec 12 15:02:28 node8 systemd[1]: Started MySQL Server.
9.4.4 配置從資料庫服務器node9
9.4.4.1 創建需要同步的資料庫及其表
[root@node9 ~]# mysql -uroot -p123456
mysql> create database hotdata; #創建熱點資料庫
Query OK, 1 row affected (0.70 sec)
mysql> use hotdata;
Database changed
#創建顧客表
mysql> create table customers(cust_id int,cust_name varchar(30),cust_address varchar(50),cust_city varchar(30),cust_state varchar(50),cust_email varchar(30),cust_country varchar(50));
Query OK, 0 rows affected (0.44 sec)
mysql> desc customers; #查看表結構
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| cust_id | int(11) | YES | | NULL | |
| cust_name | varchar(30) | YES | | NULL | |
| cust_address | varchar(50) | YES | | NULL | |
| cust_city | varchar(30) | YES | | NULL | |
| cust_state | varchar(50) | YES | | NULL | |
| cust_email | varchar(30) | YES | | NULL | |
| cust_country | varchar(50) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
7 rows in set (0.36 sec)
mysql> exit
Bye
9.4.4.2 修改node9的MySQL組態檔
[root@node9 ~]# vim /etc/my.cnf
[root@node9 ~]# cat /etc/my.cnf
[client]
port=3306
socket=/data/run/mysql.sock
default-character-set=utf8
[mysqld]
port=3306
socket=/data/run/mysql.sock
pid_file=/data/run/mysql.pid
datadir=/data/data
default_storage_engine=InnoDB
max_allowed_packet=512M
max_connections=2048
open_files_limit=65535
skip-name-resolve
lower_case_table_names=1
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
innodb_buffer_pool_size=1024M
innodb_log_file_size=2048M
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=0
key_buffer_size=64M
log-error=/data/log/mysql_error.log
slow_query_log=1
slow_query_log_file=/data/log/mysql_slow_query.log
long_query_time=5
tmp_table_size=32M
max_heap_table_size=32M
query_cache_type=0
query_cache_size=0
#mysql replication配置
server-id=3
[root@node9 ~]# systemctl restart mysqld
[root@node9 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Thu 2019-12-12 15:02:27 CST; 28s ago
Docs: man:mysqld(5.7)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 27714 ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Main PID: 27716 (mysqld)
CGroup: /system.slice/mysqld.service
└─27716 /usr/local/mysql/bin/mysqld --daemonize --pid-file=/data/run/mysql.pid
Dec 12 15:02:25 node9 systemd[1]: Starting MySQL Server...
Dec 12 15:02:27 node9 systemd[1]: Started MySQL Server.
9.4.5 在slave上配置master（所有節點）
? 四臺MySQL都扮演slave的角色，所以所有節點都要配置
#在所有節點的MySQL上執行如下
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.03 sec)
#因為node6和node9是node7的從，node7和node8是node6的從
#所以在node6和node9上執行
mysql> change master to master_host='192.168.110.188',master_user='copy',master_password='123456';
Query OK, 0 rows affected, 2 warnings (0.02 sec)
#在node7和node8上執行
mysql> change master to master_host='192.168.110.185',master_user='copy',master_password='123456';
Query OK, 0 rows affected, 2 warnings (0.04 sec)
9.4.6 啟動slave,讓四臺MySQL進入主從復制狀態
#在所有MySQL節點上執行
#mysql> start slave;
#mysql> show master status\G
#mysql> show slave status\G
#如果執行show slave status\G之后，Slave_IO_Running和Slave_SQL_Running均為yes，則說明主從同步成功
#下面以node6為例，其他節點類似
mysql> start slave;
Query OK, 0 rows affected (0.05 sec)
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 154
Binlog_Do_DB: hotdata
Binlog_Ignore_DB: mysql
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.110.188
Master_User: copy
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: node6-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000001
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: 154
Relay_Log_Space: 574
Until_Condition: None
Until_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: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: a8da7421-157f-11ea-b1bf-000c297c0226
Master_Info_File: /data/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
mysql> exit
Bye
9.4.7 測驗驗證雙主雙從
#測驗一：在node6上插入一條資料，其他所有mysql都應該同步這條資料
#測驗二:在node7上插入一條資料，其他所有mysql都應該同步這條資料
#測驗三：停止node6上的MySQL（模擬故障），在node7上插入一條資料，只有node9上同步該資料，重啟node6之后（模擬故障恢復），node6和node8也應該同步該資料，
#如果上述測驗都通過，則說明MySQL雙主雙從架構搭建完畢并功能無誤，
9.5 總結
? MySQL主從同步之多主多從架構可以實作MySQL服務的高可用，即使一個MySQL主資料庫宕機，使用另外一個主資料庫替代即可，避免了資料的不同步和服務的不可用，
十.參考資料
https://www.cnblogs.com/wuchangsoft/p/10374438.html
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/35423.html
標籤：MySQL
上一篇：mysql配置遠程登錄