MySQL數(shù)據(jù)庫(kù)集群實(shí)戰(zhàn)

一、數(shù)據(jù)庫(kù)集群使用場(chǎng)景

1.隨著訪問(wèn)量的不斷增加，單臺(tái)MySQL數(shù)據(jù)庫(kù)服務(wù)器壓力不斷地增加，需要對(duì)MySQL進(jìn)行優(yōu)化和架構(gòu)改造，如果MySQL優(yōu)化不能明顯改善壓力，可以使用高可用、主從復(fù)制、讀寫(xiě)分離出來(lái)、拆分庫(kù)、拆分表等方法來(lái)進(jìn)行優(yōu)化。

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

二、MySQL主從復(fù)制實(shí)戰(zhàn)

MySQL主從復(fù)制環(huán)境構(gòu)建至少需要2臺(tái)服務(wù)器，可以配置1主多從、多主多從，以1主1從為例，MySQL主從復(fù)制架構(gòu)實(shí)戰(zhàn)步驟如下：

1.在虛擬機(jī)上克隆一臺(tái)CentOS主機(jī)

? ?

2.開(kāi)啟兩臺(tái)CentOS 7主機(jī)，并做相關(guān)配置

1)配置兩臺(tái)CentOS主機(jī)名稱

[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??#配置永久生效的主機(jī)名

[root@localhost sky9890]# hostnamectl? ?#查看主機(jī)名稱，重新啟動(dòng)系統(tǒng)后生效

? ?Static hostname: mysql_master

? ?Pretty hostname: MySQL_Master

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

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

2)Master和Slave主機(jī)網(wǎng)絡(luò)

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創(chuàng)建用戶及授權(quán)

[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線程狀態(tài)為Yes,代表slave已正常連接master實(shí)現(xiàn)同步

? ? ? ? ? ? ? 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.測(cè)試同步結(jié)果

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操作：

通過(guò)MySQL Slave測(cè)試數(shù)據(jù)來(lái)看，主從數(shù)據(jù)庫(kù)同步成功。

7.MySQL主從同步排錯(cuò)思路

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

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

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

4)當(dāng)主從產(chǎn)生延遲后，如何忽略錯(cuò)誤后，繼續(xù)同步？

MySQL Master:

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

MySQL Slave:

MySQL [tongbu_test]> stop slave;

MySQL [tongbu_test]> set global sql_salve_skip_counter =1

MySQL [tongbu_test]> start? slave;

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

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