MySQL主從搭建的示例分析

這篇文章給大家分享的是有關MySQL主從搭建的示例分析的內容。小編覺得挺實用的，因此分享給大家做個參考，一起跟隨小編過來看看吧。

背景：

由于最近公司項目好像有點受不住并發壓力了，優化迫在眉睫。由于當前系統是單數據庫系統原因，能優化的地方也盡力優化了但是數據庫瓶頸還是嚴重限制了項目的并發能力。所以就考慮了添加數據庫來增大項目并發能力。

思路：

1： 創建集中庫： 主要就是存儲歷史數據。作為查詢使用。

2：創建多個業務庫：滿足項目高并發的能力。

demo環境：

1： VM ware 虛擬機 - centOS 7

centOS-1： 192.168.194.3 主 100-------業務庫

centOS-2： 192.168.194.4 主 200-------業務庫

centOS-3： 192.168.194.5 從 300-------相當于集中庫

2：mysql 5.7

步驟

1： 主庫100

設置my.cnf。

[mysqld]
lower_case_table_names = 1 # 表名不區分大小寫
server-id = 100
log_bin = mysql-bin  #開始binlog記錄
binlog_format = MIXED 
#每次事務提交，MySQL都會把binlog刷下去，是最安全但是性能損耗最大的設置。
#這樣的話，在數據庫所在的主機操作系統損壞或者突然掉電的情況下，系統才有可能丟失1個事務的數據
#但是binlog雖然是順序IO，但是設置sync_binlog=1，多個事務同時提交，
#同樣很大的影響MySQL和IO性能。按需設置。
sync_binlog = 1 
# 二進制日志自動刪除/過期的天數。默認值為0，表示不自動刪除。
expire_logs_days = 7
#binlog_cache_size = 128m
#max_binlog_cache_size = 512m
#max_binlog_size = 256M
# 需要同步庫
binlog-do-db = dev
# 不需要同步庫
binlog-ignore-db = mysql
binlog_ignore_db = information_schema
binlog_ignore_db = performation_schema
binlog_ignore_db = sys
datadir=/var/lib/mysql

設置slave用戶

# mysql -uroot -p 
# password: xxxxxx
# mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'root1234';
# mysql> flush privileges;
# quit;
# systemctl restart mysqld;
# show master status \G
#*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 886
     Binlog_Do_DB: dev
 Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys
Executed_Gtid_Set: 
1 row in set (0.00 sec)

2： 主庫200

設置my.cnf。

[mysqld]
lower_case_table_names = 1 # 表名不區分大小寫
server-id = 200
log_bin = mysql-bin  #開始binlog記錄
binlog_format = MIXED 
#每次事務提交，MySQL都會把binlog刷下去，是最安全但是性能損耗最大的設置。
#這樣的話，在數據庫所在的主機操作系統損壞或者突然掉電的情況下，系統才有可能丟失1個事務的數據
#但是binlog雖然是順序IO，但是設置sync_binlog=1，多個事務同時提交，
#同樣很大的影響MySQL和IO性能。按需設置。
sync_binlog = 1 
# 二進制日志自動刪除/過期的天數。默認值為0，表示不自動刪除。
expire_logs_days = 7
#binlog_cache_size = 128m
#max_binlog_cache_size = 512m
#max_binlog_size = 256M
# 需要同步庫
binlog-do-db =dev
# 不需要同步庫
binlog-ignore-db = mysql
binlog_ignore_db = information_schema
binlog_ignore_db = performation_schema
binlog_ignore_db = sys
datadir=/var/lib/mysql

設置slave用戶

# mysql -uroot -p 
# password: xxxxxx
# mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'root1234';
# mysql> flush privileges;
# quit;
# systemctl restart mysqld;
# show master status \G
#*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 154
     Binlog_Do_DB: dev
 Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys
Executed_Gtid_Set: 
1 row in set (0.00 sec)

3： 從庫300

設置my.cnf。

[mysqld]
lower_case_table_names = 1 # 表名不區分大小寫 
server-id = 300
master_info_repository = table
relay_log_info_repository = table 
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock  
symbolic-links=0 
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

設置主庫信息

# mysql -uroot -p 
# password: xxxxxx
# 設置主庫信息
# mysql> CHANGE MASTER TO  
# ->MASTER_HOST='192.168.194.3',
# ->MASTER_PORT=3306, MASTER_USER='slave', 
# ->MASTER_PASSWORD='root1234',
# ->MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=886 for channel '100';
# mysql> CHANGE MASTER TO  
# ->MASTER_HOST='192.168.194.4',
# ->MASTER_PORT=3306, MASTER_USER='slave', 
# ->MASTER_PASSWORD='root1234',
# ->MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154  for channel '200';
# 設置主庫信息
# mysql> flush privileges;
# start slave;
# mysql>show slave status \G

標識同步成功

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

測試。

隨意主庫：創建表,插入一條數據。

CREATE TABLE `t_user`  (
  `id` varchar(32) NOT NULL COMMENT '主鍵ID',
  `name` varchar(32) CHARACTER SET utf8mb4 NULL COMMENT '用戶名稱',
  `code` varchar(32) CHARACTER SET utf8mb4 NULL COMMENT '用戶編碼',
  `phone_number` varchar(300) CHARACTER SET utf8mb4 NULL COMMENT '電話號碼',
  `create_date` datetime NULL COMMENT '創建時間',
  `update_date` datetime NULL COMMENT '修改時間',
  PRIMARY KEY (`id`)
) COMMENT = '用戶信息表';
INSERT INTO t_user (`id`, `name`, `code`, `phone_number`, `create_date`, `update_date`) 
VALUES ('userId_4', '張三', '123456789', '123456789632', '2020-04-27 22:05:00', '2020-04-27 22:05:00');

從庫查詢

SELECT * FROM t_user;
------+--------+----------+--------------+---------------------+-------------+
| id       | name   | code     | phone_number | create_date  | update_date |
+-------+--------+----------+--------------+---------------------+----------+
| userId_4 | 張三 |123456789|123456789632|2020-04-27 22:05:00|2020-04-27 22:05:00

感謝各位的閱讀！關于“MySQL主從搭建的示例分析”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！