High Performance MySQL Summary

Directory

1. MySQL Master-slave replication
2. Copy Filter
3. Replication and Monitoring
4. Read and write separation of master-slave replication
5. Backup and Recovery
6. Table partitioning

First, MySQL master-slave replication

Replication topology:

Host	IP	Notes
Mysql-master.linux.com	192.168.239.142	Master
Mysql-slave.linux.com	192.168.239.143	Slave

1. Create a copy Account

Connect to the primary database server and create an account with replication slave and replication client permissions.

[[email protected] ~]# mysqlmysql> grant replication slave,replication client on *.* to [email protected]‘192.168.239.%‘ identified by ‘centos‘;

2, configure the master-slave node

Master node:

[[email protected] ~]# vim /etc/my.cnf [mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0# 新增加如下两行log_bin=/data/mysql/logs/mysql-binserver_id=10[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

LOG_BIN Specifies the directory and filename of the generated binary days file
SERVER_ID Specifies the database ID that uniquely identifies
From node:

[[email protected] ~]# vim /etc/my.cnf [mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0# 新增如下server_id=20relay_log=/data/mysql/logs/mysql-relay-binread_only=1[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

Relay_log specifying the directory and file name of the trunk log
Finally, the master-slave database service is restarted.

3. Connect the master node from the node

[[email protected] ~]# mysqlmysql> change master to master_host=‘192.168.239.142‘,master_user=‘replicater‘,master_password=‘centos‘,master_log_file=‘mysql-bin.000003‘,master_log_pos=283;

where Master_log_file and Master_log_pos can be queried according to the main node's show master status statement.

[[email protected] ~]# mysqlmysql> show slave status\G;

You can see that the IO thread from the node and the SQL thread are not started. That is because replication from the node has not yet started.

After you start replication from the node, you can see the IO thread from the node and the SQL thread start.

mysql> start slave;

So far, MySQL master-slave replication has been configured, and the node will change as soon as the data of the host node changes. For example:

The MyDB database is then generated from the node accordingly.

It is also possible to see that the location of the read binaries has changed. (increased from 283 to 336)

Second, copy filter 1, the main library filter

Filtering on the main library is restricted to the database and cannot be filtered against the table.
binlog_do_db= specifies the database to which the update data is written to the binary log file, whitelist
binlog_ignore_db= specifies that the update data is not written to the binary database, blacklist

2. Filter from Library

From the library's SQL thread only to the concerned database, the table replay, in order to realize the filter from the library, you can find that the filter from the library is not only for the database, but also for the table.
replicate_do_db=
replicate_ignore_db=

replicate_do_table=
replicate_igonre_table=

replicate_wild_do_table=
replicate_wild_ignore_table=

Third, the monitoring and maintenance of replication

Over time, the log files in the database accumulate over the years and consume a lot of disk space. Therefore, some log files need to be managed.

1. Clean up the log

For the binary log file of the database, can not simply use the RM command to delete, if deleted directly, the index file used to record the binary log file is not modified, which will cause the database replication error. It is therefore necessary to use specialized tools to clean up the logs.
Command purge:
Format: PURGE master| BINARY LOGS to ' log_name '

2. Replication Monitoring

Main Library:
? SHOW MASTER STATUS;
? SHOW BINLOG EVENTS;
? SHOW BINARY LOGS;



From library:
? SHOW SLAVE Status\g;

The Seconds_behind_master parameter indicates how long it takes to lag behind the main library from the library. To resolve the problem of master-slave data inconsistency, you can try restarting the SQL thread.

Iv. Read and write separation of master-slave replication

Implemented with SQL Router (Read/write spliter).
There are many middleware to realize the MySQL cluster reading and writing separation, which uses proxysql to realize the separation of reading and writing.
Environment topology:

IP Address	role
192.168.239.129	Proxysql Host
192.168.239.142	MySQL Master node
192.168.239.143	MySQL slave node

Specific operation:

1, MySQL master-slave node to achieve master-slave replication

Examples of this step 一、MySQL主从复制 Reference

2. Installing Proxysql Middleware

Proxysql for Https://github.com/sysown/proxysql/releases.
There is a corresponding version of the operating system, download can

[[email protected] src]# pwd/usr/local/src[[email protected] src]# ls proxysql-1.4.10-1-centos67.x86_64.rpm proxysql-1.4.10-1-centos67.x86_64.rpm[[email protected] src]# yum -y install proxysql-1.4.10-1-centos67.x86_64.rpm

Proxysql provides the following files. The/etc/proxysql.cnf file is the initialization configuration at the start of the Proxysql service.

The following operations can be directly configured directly in the/ETC/PROXYSQL.CNF, start the Proxysql service can be a step in place; Of course, you can also connect the Proxysql through the management side (referred to below) and complete the configuration step-by-step with SQL statements.
This is done through/ETC/PROXYSQL.CNF.

3. Create Proxysql Management Account

Proxysql will launch two types of ports, a management port for managing configuration proxysql, local login only, and a class of client ports for connecting to the back end of MySQL. They are monitored by default on 6032 and 6033 ports respectively.
First configure the management side of the Proxysql to create a management account.
Add the following content:

which

Parameters	function
Admin_credentials	Specify the user and password to connect to the management side
Mysql_ifaces	Specify the IP and port of the connection management side

4, configure the backend MySQL

The relevant configuration content is as follows:

mysql_servers =(    {        address = "192.168.239.142" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain        port = 3306           # no default, required . If port is 0 , address is interpred as a Unix Socket Domain        hostgroup = 1           # no default, required        status = "ONLINE"     # default: ONLINE        weight = 1            # default: 1        compression = 0       # default: 0    max_replication_lag = 10  # default 0 . If greater than 0 and replication lag passes such threshold, the server is shunned    },    {        address = "192.168.239.143"        port = 3306        hostgroup = 2        status = "ONLINE"        weight = 1        compression = 0        max_replication_lag = 10    })

Address? Specify the IP address of the back-end MySQL
Port? Specifies the backend MySQL port
HostGroup? Specifies the MySQL group ID, which is planned for 1 groups for write operations, 2 for read operations

5. Create an account connected to MySQL

This step requires two MySQL hosts on the back end, respectively. Create the Connection account Proxysql separately and give the account full privileges. The account can also be connected to the backend MySQL by logging in to the Proxysql host.
Master node:

[[email protected] ~]# mysql ([email protected]) [none]> grant all on *.* to [email protected]‘192.168.239.%‘ identified by ‘proxysql‘;

From node:

[[email protected] ~]# mysql ([email protected]) [none]> grant all on *.* to [email protected]‘192.168.239.%‘ identified by ‘proxysql‘;

Then add the connection to the MySQL account Proxysql to the Proxysql host, the Proxysql host is through this account to connect to the backend MySQL and operation. Add the following to the/ETC/PROXYSQL.CNF

mysql_users:(    {        username = "proxysql" # no default , required        password = "proxysql" # default: ‘‘        default_hostgroup = 1 # default: 0        active = 1            # default: 1    })

Default_hostgroup=1 indicates that the PROXYSQL host defaults to dispatching SQL events to 1 groups, typically the one for which the write operation occurs.

6. Add rules to proxysql host

In/etc/proxysql.cnf, add the following:

mysql_query_rules:(    {        rule_id=1        active=1        match_pattern="^SELECT .* FOR UPDATE$"        destination_hostgroup=1        apply=1    },    {        rule_id=2        active=1        match_pattern="^SELECT"        destination_hostgroup=2        apply=1    })

The query statement at the start of select is dispatched to 2 groups (that is, the group of Read operations), but special attention is paid to statements such as the Select ... for update, which also modifies the data, so it needs to be dispatched to a group of 1 (that is, a write operation), and all the remaining SQL statements are dispatched to Default_ The default group defined by the HostGroup.
As to whether to define monitoring users, for simplicity, this is not created in back-end MySQL, monitoring users can monitor the status information of the backend MySQL node, and can be automatically defined as read-only groups based on the read_only parameters of the backend MySQL. And because the read and write groups are already defined in Mysql_servers =, there is no need to create a monitoring user.
/ETC/PROXYSQL.CNF other content remains the default. Then start the Proxysql service.

7. Test read/write separation

Connect Proxysql host through Proxysql account, make related query operation and change data operation respectively.

[[email protected] ~]# mysql -uproxysql -h127.0.0.1 -P6033 -pproxysql([email protected]) [(none)]> select user,host from mysql.user;([email protected]) [(none)]> create database mydb2;

Then log in Proxysql by managing your account to see the specific connection status

[[email protected] ~]# mysql -uadmin -h127.0.0.1 -P6032 -padmin([email protected]) [(none)]> select * from stats_mysql_query_digest;

You can see that the read operation was dispatched to 2 groups, and the write operation dispatched 1 groups. Read/write separation succeeded.

V. Backup and Recovery 5.1 logical Backup tool mysqldump

Mysqldump is a tool for logical backup that comes with MySQL.

Here are a few examples of common ways to mysqldump tools:

1. Back up all databases to a single file

# 将192.168.239.143主机的mysql上所有库备份到本地root/backup目录的dump.sql文件mysqldump --user=root --host=192.168.239.143 --password=centos --all-databases > /root/backup/dump.sql

2. Backing up a single database to a single file

# 将192.168.239.143主机的mysql上的mydb库备份到本地的dump.sql文件mysqldump --user=root --host=192.168.239.143 --password=centos --databases mydb > /root/backup/dump.sql

3. Back up a single data table to a single file

# 将192.168.239.143主机上的mysql的mydb中的mytbl表备份到本地dump.sql文件mysqldump --user=root --host=192.168.239.143 --password=centos --databases mydb --tables mytbl > /root/backup/dump.sql# 将备份的单个文件恢复到mydb库中mysql -u root mydb < /root/backup/dump.sql

If you get an error restoring a mysqldump backed up table: "Error 1046 (3d000) at line 22:no database selected, because there is no specified database in which to restore the table.

4. Mysqldump full-scale backup +mysqlbinlog incremental backup

You first need to turn on the MySQL service binary logging feature and add Log_bin=/data/mysql/logs/mysql-bin to/ETC/MY.CNF

[[email protected] ~]# mkdir -p /data/mysql/logs[[email protected] ~]# chown mysql.mysql /data/mysql/logs

Make a full backup of all databases at a time,

[[email protected] ~]# mysqldump --single-transaction  --flush-logs --master-data=2 --user=root --host=127.0.0.1 > /root/backup/dump.`date +%F`.sql[[email protected] ~]# cd /root/backup/[[email protected] backup]# lsdump.2018-08-31.sql

View data information for full-scale backups,

Now add the new data again,

([email protected]) [(none)]> insert into mydb.mytbl value (6,‘huangzhong‘,90),(7,‘zhugeliang‘,20);

All databases have been deleted because of misoperation.

[[email protected] ~]# rm -rf /var/lib/mysql/*

Here, if you just rely on a full-scale backup for recovery, MySQL can only go back to the point of backup data, and then the new data is not recoverable, so you also need to generate incremental backups with binary files.
Now for incremental backups, you need to use the latest generated binaries.

[[email protected] ~]# mysqlbinlog /data/mysql/logs/mysql-bin.000001 > /root/backup/mysql-bin.000001.sql

This allows the existence of two SQL files in the/root/backup/directory, a full-scale backup, and an incremental backup.
Now start restoring all data before deleting the library.
Restart the MYSQLD service,

[[email protected] backup]# /etc/init.d/mysqld restart[[email protected] backup]# mysql < /data/backup/dump.2018-08-31.sql# 恢复全量备份之后，数据库中还没有后来新增的那两条数据[[email protected] backup]# mysql < /data/backup/mysql-bin.000001.sql# 恢复增量备份，恢复新增的数据

5.2 Physical Backup tool xtrabackup

Xtrabackup: https://www.percona.com/downloads/XtraBackup/LATEST/

1. Xtrabackup full-scale backup and recovery

Use the Innobackupex command in the Xtrabackup tool to perform a full-scale backup of the database, which will generate a backup directory in the specified directory after completion

[[email protected] ~]# innobackupex --user=root /root/backup/[[email protected] ~]# cd /root/backup/[[email protected] backup]# ls2018-08-31_02-17-02

You can see that the backup directory is similar to the data in the original database.

In the preparation phase, after the second phase, you can not connect to MySQL. So pause the MySQL service.
Format of preparation phase: Innobackupes--appply-log < Full backup directory generated >

[[email protected] 2018-08-31_02-17-02]# /etc/init.d/mysqld stop[[email protected] 2018-08-31_02-17-02]# innobackupex --apply-log /root/backup/2018-08-31_02-17-02/

Because all databases were deleted by mistake,

[[email protected] 2018-08-31_02-17-02]# rm -rf /var/lib/mysql/*

for recovery,

[[email protected] 2018-08-31_02-17-02]# innobackupex --copy-back /root/backup/2018-08-31_02-17-02/

In summary, the Xtrabackup tool recovers data operations through three stages: backup, preparation, recovery. When you see the completed ok! the words indicate that each stage succeeds.
The owner and group of files and directories generated in the data directory after completion are root and need to be changed to MySQL

[[email protected] mysql]# chown -R mysql.mysql /var/lib/mysql/*

Then start MySQL and you can see that the data is back again.

([email protected]) [(none)]> select * from mydb.mytbl;

2. Xtrabackup incremental backup and recovery

The full-scale backup of the above example is a benchmark to make an incremental backup,

[[email protected] ~]# mkdir /root/backup/incremental[[email protected] ~]# innobackupex --user=root  --incremental /root/backup/incremental --incremental-basedir=/root/backup/2018-08-31_02-17-02/

Insert the new data in the database again,

Do an incremental backup again, this time the incremental backup is the benchmark,

[[email protected] incremental]# innobackupex --user=root --incremental /root/backup/incremental --incremental-basedir=/root/backup/incremental/2018-08-31_03-02-49/

This generates two incremental backup files.

All databases have been deleted because of misoperation

[[email protected] ~]# rm -rf /var/lib/mysql/*

Start the recovery operation now. Pause MySQL Service first
1. Prepare for full-scale backup

[[email protected] ~]# innobackupex --apply-log --redo-only /root/backup/2018-08-31_02-17-02

2. Combine incremental backups to add two incremental backups to a full-scale backup

# 增量1增加到全量备份[[email protected] ~]# innobackupex --apply-log --redo-only /root/backup/2018-08-31_02-17-02 --incremental-dir=/root/backup/incremental/2018-08-31_03-02-49# 增量2增加到全量备份[[email protected] ~]# innobackupex --apply-log /root/backup/2018-08-31_02-17-02/ --incremental-dir=/root/backup/incremental/2018-08-31_03-10-59/

3. Prepare for full backup with incremental backup added

[[email protected] ~]# innobackupex --apply-log /root/backup/2018-08-31_02-17-02/

4. Start recovery

[[email protected] ~]# innobackupex --copy-back /root/backup/2018-08-31_02-17-02/[[email protected] ~]# chown -R mysql.mysql /var/lib/mysql/*

Now look at the reply after the database, you can find all the data are back.

Note: This option is used in the "Preparing basic full Backup" and "merging all incremental backups (except the last one)" in the meaning of--redo-only. It is passed directly to Xtrabackup's xtrabackup--apply-log-only option, allowing Xtrabackup to skip the "undo" phase and do only "redo" operations. This is necessary if there is an incremental backup to apply to this fully prepared back.

Vi. Partitioning of tables

MySQL supports four types of partitions, respectively:
Range partition
List partition
Hash partition
Key partition

1. Range partition type

This type of partition is partitioned based on successive values.
For example, create a students table, and partition based on an ID of three partitioned tables with an ID less than 101,id less than 201,id less than 301.

([email protected]) [mydb1]> create table students (id INT,name char(20),grade float(3,1)) partition by range (id) ( partition p0 values less than (101),partition p1 values less than (201),partition p2 values less than (301) );

2. List partition type

This type of partition is partitioned based on discrete values.
For example, create a cla***ooms table, where Room-id is 11,12,13,14 in the Pfirst partition table, 21,22,23,24 in the Psecond partition table, 31,32,33,34 in the Pthird partition table.

([email protected]) [mydb1]> create table cla***ooms (id INT,leader char(20)) partition by list(id) ( partition pfirst values in (11,12,13,14),partition psecond values in (21,22,23,24),partition pthird values in (31,32,33,34) );

3. Hash partition type

This type of partition is hash based on different values, and then partitioned by hash value. The key for the hash partition must be an integer.
For example, create a teachers table with hash operations based on the ID field value, in the syntax format: partition by hash (expression) partitions num. Expresssion represents an expression that returns an integer, and num represents the number of partitions.

([email protected]) [mydb1]> create table teachers (id INT,name char(20),age TINYINT) partition by hash (id) partitions 3;

4. Key partition type

This type of partition is also based on different values for hash operations, and hash partitioning is different, the key partition supports non-integer keys.

([email protected]) [mydb1]> create table employee (name char(20),birthday date,job char(30)) partition by  key (birthday) partitions 3;

High Performance MySQL Summary