Shulou(Shulou.com)06/01 Report--

I. usage scenario of database cluster

1. With the continuous increase of access volume, the pressure of a single MySQL database server continues to increase, so it is necessary to optimize and transform the MySQL. If MySQL optimization can not significantly improve the pressure, we can use high availability, master-slave replication, read-write separation, split library, split table and other methods to optimize.

2.MySQL master-slave replication cluster is widely used in small and medium-sized enterprises and large enterprises. MySQL master-slave replication aims to achieve redundant data backup and synchronize master database data to slave database on a regular basis. Once the master database is down, you can quickly switch the configuration of web application database to salve database to ensure the high availability of Web applications. The MySQL master-slave replication architecture diagram is shown in figure 1-1.

II. MySQL master-slave copy actual combat

The construction of MySQL master-slave replication environment requires at least 2 servers. You can configure 1 master and multi-slave and multi-master and multi-slave. Take one master and one slave as an example. The practical steps of MySQL master-slave replication architecture are as follows:

1. Clone a CentOS host on a virtual machine

two。 Turn on two CentOS 7 hosts and configure them

1) configure two CentOS host names

[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 # configure a permanent hostname

[root@localhost sky9890] # hostnamectl # View the host name and take effect after restarting the system

Static hostname: mysql_master

Pretty hostname: MySQL_Master

.

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

2) Master and Slave host network

MySQL Master:192.168.153.142 # configure 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 # configure 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 configuration

[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 creates users and authorizes

[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 configuration

[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, the thread status of SQL is Yes, which means that slave is normally connected to master to achieve synchronization

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. Test synchronization results

MySQL Master operation:

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", "Wu Family")

MySQL Slave operation:

According to the MySQL Slave test data, the master-slave database synchronization is successful.

The idea of 7.MySQL master-slave synchronous troubleshooting

1) server-id, master and slave cannot be the same.

2) slave specifies that the information of master IP, user name, password, bin-log file name and position should be the same.

3) Slave_IO_Runngin:Yes Slave_SQL_Runngin:Yes, only if both states are Yes, can slave synchronization be considered successful.

4) after the master-slave delay, how to ignore the error and continue to synchronize?

MySQL Master:

MySQL [(none)] > flush tables with read block; # sets the database as a global read lock and does not allow new data to be written.

MySQL Slave:

MySQL [tongbu_test] > stop slave

MySQL [tongbu_test] > set global sql_salve_skip_counter = 1

MySQL [tongbu_test] > start slave

Note that the above steps should be performed at least once, and it may take twice to solve the problem.

Finally, unlock the master: MySQL [(none)] > unlock tables

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.