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;