MySQL數據庫集群實戰

一、數據庫集群使用場景

1.隨著訪問量的不斷增加，單臺MySQL數據庫服務器壓力不斷地增加，需要對MySQL進行優化和架構改造，如果MySQL優化不能明顯改善壓力，可以使用高可用、主從復制、讀寫分離出來、拆分庫、拆分表等方法來進行優化。

2.MySQL主從復制集群在中小企業、大型企業中被廣泛應用，MySQL主從復制的目的實現數據冗余備份，將master數據庫數據定時同步至slave庫中，一旦master數據庫宕機，可以將web應用數據庫配置快速切換到salve數據庫，確保Web應用有較高的可用率，MySQL主從復制架構圖如圖1-1所示。

二、MySQL主從復制實戰

MySQL主從復制環境構建至少需要2臺服務器，可以配置1主多從、多主多從，以1主1從為例，MySQL主從復制架構實戰步驟如下：

1.在虛擬機上克隆一臺CentOS主機

? ?

2.開啟兩臺CentOS 7主機，并做相關配置

1)配置兩臺CentOS主機名稱

[root@localhost sky9890]# hostnamectl?

? ?Static hostname: #localhost.localdomain

Transient hostname: localhost.localdomain

? ? ? ? ?Icon name: computer-vm

? ? ? ? ? ?Chassis: vm

? ? ? ? Machine ID: 6c938bf5dc5b492088dafb0e745f01ec

? ? ? ? ? ?Boot ID: 170db1b33955402daa0ee3d6911486ba

? ? Virtualization: vmware

? Operating System: CentOS Linux 7 (Core)

? ? ? ?CPE OS Name: cpe:/o:centos:centos:7

? ? ? ? ? ? Kernel: Linux 3.10.0-862.11.6.el7.x86_64

? ? ? Architecture: x86-64

[root@localhost sky9890]# hostnamectl? set-hostname? MySQL_Master??#配置永久生效的主機名

[root@localhost sky9890]# hostnamectl? ?#查看主機名稱，重新啟動系統后生效

? ?Static hostname: mysql_master

? ?Pretty hostname: MySQL_Master

..................................................................

[root@localhost sky9890]# hostnamectl set-hostname MySQL_Slave

2)Master和Slave主機網絡

MySQL Master：192.168.153.142? #配置Master IP

[root@localhost sky9890]# vim? /etc/sysconfig/network-scripts/ifcfg-eth0?

TYPE="Ethernet"

BOOTPROTO="static"

NAME="ens33"

UUID="9f75af90-bd5d-467e-b433-216456e4a49e"

DEVICE="eth0"

ONBOOT="yes"

IPADDR=192.168.153.142

NETMASK=255.255.255.0

GATEWAY=192.168.153.2

MySQL Slave：192.168.153.143? ? #配置Slave IP

[root@localhost sky9890]# vim /etc/sysconfig/network-scripts/ifcfg-eth0?

TYPE="Ethernet"

BOOTPROTO="static"

DEFROUTE="yes"

IPV4_FAILURE_FATAL="no"

IPV6INIT="yes"

IPV6_AUTOCONF="yes"

IPV6_DEFROUTE="yes"

IPV6_FAILURE_FATAL="no"

IPV6_ADDR_GEN_MODE="stable-privacy"

NAME="ens33"

DEVICE="eth0"

ONBOOT="yes"

IPADDR=192.168.153.143

NETMASK=255.255.255.0

GATEWAY=192.168.153.2

3.MySQL Master配置

[root@mysql_master sky9890]# vim? /etc/my.cnf? ?

[client]

port? ? ? ? ? ? = 3306

socket? ? ? ? ? = /tmp/mysql.sock

[mysqld]

port? ? ? ? ? ? = 3306

socket? ? ? ? ? = /tmp/mysql.sock

skip-external-locking

key_buffer_size = 256M

max_allowed_packet = 1M

table_open_cache = 256

sort_buffer_size = 1M

read_buffer_size = 1M

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size= 16M

thread_concurrency = 8

log-bin=mysql-bin

binlog_format=mixed

server-id? = 1

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size = 128M

sort_buffer_size = 128M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

4.MySQL Master創建用戶及授權

[root@mysql_master etc]# useradd testtongbu

[root@mysql_master etc]# passwd testtongbu?

[root@mysql_master etc]# mysql -uroot? -p

MySQL [(none)]> grant replication slave on *.*? to? 'testtongbu '@'%'? identified by '12345678';?

MySQL [(none)]> show master status;

+------------------+----------+--------------+------------------+

| File? ? ? ? ? ? ?| Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000008 |? ? ? 867 |? ? ? ? ? ? ? |? ? ? ? ? ? ? ? ? |

+------------------+----------+--------------+------------------+

5.MySQL Slave配置

[root@mysql_master sky9890]# vim? /etc/my.cnf? ?

[client]

port? ? ? ? ? ? = 3306

socket? ? ? ? ? = /tmp/mysql.sock

[mysqld]

port? ? ? ? ? ? = 3306

socket? ? ? ? ? = /tmp/mysql.sock

skip-external-locking

key_buffer_size = 256M

max_allowed_packet = 1M

table_open_cache = 256

sort_buffer_size = 1M

read_buffer_size = 1M

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size= 16M

thread_concurrency = 8

#log-bin=mysql-bin

#binlog_format=mixed

server-id? = 2

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size = 128M

sort_buffer_size = 128M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

[root@mysql_slave sky9890]#

MySQL [(none)]> change master to master_host='192.168.153.142', master_port=3306, master_user='testtongbu',?

master_passwork='12345678', master_log_file='mysql-bin.000008', master_log_pos=867;

MySQL [(none)]> slave start;

MySQL [(none)]> show slave status\G

*************************** 1. row ***************************

? ? ? ? ? ? ? ?Slave_IO_State: Waiting for master to send event

? ? ? ? ? ? ? ? ? Master_Host: 192.168.153.142

? ? ? ? ? ? ? ? ? Master_User: testtongbu

? ? ? ? ? ? ? ? ? Master_Port: 3306

? ? ? ? ? ? ? ? Connect_Retry: 60

? ? ? ? ? ? ? Master_Log_File: mysql-bin.000008

? ? ? ? ? Read_Master_Log_Pos: 867

? ? ? ? ? ? ? ?Relay_Log_File: mysql_slave-relay-bin.000005

? ? ? ? ? ? ? ? Relay_Log_Pos: 596

? ? ? ? Relay_Master_Log_File: mysql-bin.000008

? ? ? ? ? ? ?Slave_IO_Running: Yes

? ? ? ? ? ? Slave_SQL_Running: Yes? ? #IO、SQL線程狀態為Yes,代表slave已正常連接master實現同步

? ? ? ? ? ? ? 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: 867

? ? ? ? ? ? ? Relay_Log_Space: 904

? ? ? ? ? ? ? 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

6.測試同步結果

MySQL Master操作：

MySQL [(none)]> create database tongbu_test charset=utf8;

MySQL [(none)]> use? tongbu_test;

MySQL [tongbu_test]> create table test(id varchar(20),name varchar(20));

MySQL [tongbu_test]> show tables;

MySQL [tongbu_test]> create table student(id varchar(20),name varchar(20));

ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock

MySQL [tongbu_test]> unlock tables;

MySQL [tongbu_test]> create table student(id varchar(20),name varchar(20));

MySQL [(none)]> insert into student values("001","吳氏親宗");

MySQL Slave操作：

通過MySQL Slave測試數據來看，主從數據庫同步成功。

7.MySQL主從同步排錯思路

1)server-id,主從不能相同。

2)slave指定master IP、用戶名、密碼、bin-log文件名及position的信息要一致。

3)Slave_IO_Runngin:Yes? Slave_SQL_Runngin:Yes，只有這兩個狀態都為Yes,才算是正從同步成功。

4)當主從產生延遲后，如何忽略錯誤后，繼續同步？

MySQL Master:

MySQL [(none)]> flush tables with read block; #將數據庫設置為全局讀鎖，不允許寫入新數據。

MySQL Slave:

MySQL [tongbu_test]> stop slave;

MySQL [tongbu_test]> set global sql_salve_skip_counter =1

MySQL [tongbu_test]> start? slave;

注意以上幾步至少要操作一次，有可能要兩次才能解決問題。

最后將master端解鎖：MySQL [(none)]> unlock tables;