MySQL所有的主從同步架構搭建方式

来源:https://www.cnblogs.com/renshengdezheli/archive/2020/05/28/12979504.html
-Advertisement-
Play Games

一.前言 ​ 本文將指導搭建所有的MySQL主從同步架構方案： ​ 一主多從架構 ​ 主主雙向同步架構 ​ M-S-S三級級聯同步架構 ​ 多主多從架構 二.關於MySQL主從同步 ​ MySQL主從同步是構建大型，高性能應用的基礎，MySQL主從同步可以實現在從伺服器可以執行查詢工作(即我們常說的 ...


目錄

一.前言

​ 本文將指導搭建所有的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

​ 1575168018572

五.四台機器上使用通用二進位包安裝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拷貝

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • CentOS基礎命令大全 （轉）
    1.關機 (系統的關機、重啟以及登出 ) 的命令 shutdown -h now 關閉系統(1) init 0 關閉系統(2) telinit 0 關閉系統(3) shutdown -h hours:minutes & 按預定時間關閉系統 shutdown -c 取消按預定時間關閉系統 shutdo ...
  • corosync v1 + pacemaker高可用集群部署（二）資源配置(VIP+httpd+NFS)
    實驗目的：使用corosync v1 + pacemaker部署httpd高可用服務（+NFS）。本實驗使用Centos 6.8系統，FileSystem資源伺服器，NA1節點1，NA2節點2，VIP192.168.94.222目錄結構：（好煩啊，佈局一塌糊塗）1、corosync v1 + pac... ...
  • 在 Linux 系統中如何管理 systemd 服務
    在上一篇文章《Linux的運行級別與目標》中，我介紹過 Linux 用 systemd 來取代 init 作為系統的初始化進程。儘管這一改變引來了很多爭議，但大多數發行版，包括 RedHat、Fedora、CentOS、Debian、Ubuntu、openSUSE、Arch 等等都已經做出了調整。不 ...
  • SQL-Base 用表組織數據
    什麼是數據完整性？ 數據完整性 = 數據準確性 + 數據可靠性 數據完整性如何分類？ 1，實體完整性 （行） 2，域完整性 （列） 3，引用完整性 （表與表之間鍵的關係） 4，自定義完整性 如何保證數據的完整性？ 必須在建表時分別對資料庫實施完整性約束： 實體完整性約束 （行） 主鍵約束（prima ...
  • Mycat安裝部署簡單使用
    Mycat安裝部署簡單使用1. 軟體準備Mycat:下載地址：https://github.com/MyCATApache/Mycat-download。下載界面圖 1‑1 Mycat首頁下載界面。任意選中RELEASE版本，進入RELEASE版本目錄，如下所示圖 1‑2 1.4RELEASE目錄下 ...
  • MySQL所有的安裝部署方式
    一.前言 ​ linux安裝軟體的方式多種多樣，MySQL也不例外，本文將介紹MySQL所有的安裝方式。 二.關於MySQL的安裝 ​ MySQL一般可以採用四種安裝方式，每種方式各有優點，使用場景各有不同： yum安裝MySQL，優點：簡單，方便，適用場景：可以訪問網路的環境 離線源碼編譯安裝My ...
  • mysql 給查詢結果新增一個欄位，並設定預設值
    SELECT '第一中學' as school, class, name, geography FROM test_table WHERE test = 1; 插入新table： REPLACE INTO test_school(school, class, name, geography) SEL ...
  • 50個SQL語句(MySQL版) 問題十二
    表結構 student(StuId,StuName,StuAge,StuSex) 學生表 teacher(TId,Tname) 教師表 course(CId,Cname,C_TId) 課程表 sc(SId,S_CId,Score) 成績表 問題十二：查詢至少學過學號為“1001”同學所有課程的其他同 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類