1.Linux under MySQL to create a user backup role, and granted the role of SELECT, RELOAD, SHOW DATABASES, LOCK TABLES and other permissions.

mysql> create user 'backuper'@'localhost' identified by '********';
Query OK, 0 rows affected (0.00 sec)

mysql> grant SELECT, RELOAD, SHOW DATABASES, LOCK TABLES on *.* to backuper@localhost;
Query OK, 0 rows affected (0.00 sec)

2. In the system to find a relatively large hard disk to create a backup directory, and create a shell script

vim backup_mohan_db.sh

#!/bin/bash
mysqldump -ubackuper -p******** qianyu_veeker_db > /home/mysql/backup/mohan_db_$(date +%Y%m%d_%H%M%S)

3. To add the scheduled task, you need to install the crontab 
vixie-cron package is the main program for cron; the 
crontabs package is the program used to install, uninstall, or list the tables used to drive the cron daemon.

[root@~]# yum -y install vixie-cron
[root@~]# yum -y install crontabs

4. Set boot to start

[root@ ~]# chkconfig --level 345 crond on

[root@linuxidc etc]# vim crontab

SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root
HOME=/

# For details see man4 crontabs

# Example of job definition:
# .---------------- minute (0 - 59)
#|  .------------- hour (0 - 23)
#|  |  .---------- day of month (1 - 31)
#|  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ...
#|  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
#|  |  |  |  |
#*  *  *  *  * user-name command to be executed
0 0 * * * /home/mysql/backup/mohan_db.sh

1. Background
MySQL database centralized operation and maintenance, through a server, the deployment of running multiple MySQL service process, through a different socket to monitor different service ports to provide their services. Each instance is independent of each other, each instance of the datadir, port, socket, pid are different.

2. Multi-instance features
* Effective use of server resources, when a single server resources are left, you can make full use of the remaining resources to provide more services.
* Resources to each other to seize the problem, when a service instance service is high or open slow query, it will consume more memory, CPU, disk IO resources, resulting in other instances of the server to provide services to reduce the quality.

[root@MySQL ~]# cat /etc/RedHat-release
CentOS release 6.9 (Final)

[root@MySQL ~]# uname -r
2.6.32-696.3.2.el6.x86_64

[root@MySQL ~]# getenforce
Disabled

[root@MySQL ~]# tar zxvf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz -C /usr/local/

[root@MySQL ~]# ln -s /usr/local/mysql-5.7.18-linux-glibc2.5-x86_64 /usr/local/mysql

[root@MySQL ~]# useradd -r -s /sbin/nologin mysql

[root@MySQL ~]# mkdir -v /usr/local/mysql/mysql-files
mkdir: created directory `/usr/local/mysql/mysql-files’

[root@MySQL ~]# mkdir -vp /data/mysql_data{1..4}
mkdir: created directory `/data’
mkdir: created directory `/data/mysql_data1′
mkdir: created directory `/data/mysql_data2′
mkdir: created directory `/data/mysql_data3′
mkdir: created directory `/data/mysql_data4′

1 [root@MySQL ~]# chown root.mysql -R /usr/local/mysql-5.7.18-linux-glibc2.5-x86_64

[root@MySQL ~]# chown mysql.mysql -R /usr/local/mysql/mysql-files /data/mysql_data{1..4}

/etc/my.cnf
[mysqld_multi]
mysqld    = /usr/local/mysql/bin/mysqld
mysqladmin = /usr/local/mysql/bin/mysqladmin
log        = /tmp/mysql_multi.log

[mysqld1]

datadir = /data/mysql_data1

socket = /tmp/mysql.sock1

port = 3306

user = mysql

performance_schema = off

innodb_buffer_pool_size = 32M

bind_address = 0.0.0.0

skip-name-resolve = 0

[mysqld2]
datadir = /data/mysql_data2
socket = /tmp/mysql.sock2
port = 3307
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
bind_address = 0.0.0.0
skip-name-resolve = 0

[mysqld3]
datadir = /data/mysql_data3
socket = /tmp/mysql.sock3
port = 3308
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
bind_address = 0.0.0.0
skip-name-resolve = 0

[mysqld4]
datadir = /data/mysql_data4
socket = /tmp/mysql.sock4
port = 3309
user = mysql
performance_schema = off
innodb_buffer_pool_size = 32M
bind_address = 0.0.0.0
skip-name-resolve = 0

[root@MySQL ~]# /usr/local/mysql/bin/mysqld –initialize –user=mysql –basedir=/usr/local/mysql –datadir=/data/mysql_data1
[root@MySQL ~]# /usr/local/mysql/bin/mysqld –initialize –user=mysql –basedir=/usr/local/mysql –datadir=/data/mysql_data2
[root@MySQL ~]# /usr/local/mysql/bin/mysqld –initialize –user=mysql –basedir=/usr/local/mysql –datadir=/data/mysql_data3
[root@MySQL ~]# /usr/local/mysql/bin/mysqld –initialize –user=mysql –basedir=/usr/local/mysql –datadir=/data/mysql_data4

[root@MySQL ~]# cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi

[root@MySQL ~]# chmod +x /etc/init.d/mysqld_multi

[root@MySQL ~]# chkconfig –add mysqld_multi

[root@MySQL ~]# /etc/init.d/mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld3 is not running
MySQL server from group: mysqld4 is not running

[root@MySQL ~]# /etc/init.d/mysqld_multi start

Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
MySQL server from group: mysqld4 is running

[root@MySQL ~]# netstat -lntp | grep mysqld
tcp        0      0 0.0.0.0:3306        0.0.0.0:*                LISTEN      2673/mysqld
tcp        0      0 0.0.0.0:3307        0.0.0.0:*                LISTEN      2676/mysqld
tcp        0      0 0.0.0.0:3308        0.0.0.0:*                LISTEN      2679/mysqld
tcp        0      0 0.0.0.0:3309        0.0.0.0:*                LISTEN      2682/mysqld

[root@MySQL ~]# /usr/local/mysql/bin/mysql -S /tmp/mysql.sock1  -p’z+Ilo*>s:3kw’
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.18

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> set password = ‘123456’;
Query OK, 0 rows affected (0.00 sec)

[root@MySQL ~]# /usr/local/mysql/bin/mysql -S /tmp/mysql.sock2  -p’b*AHUrTgu1rl’
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.18

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> set password = ‘123456’;
Query OK, 0 rows affected (0.00 sec)

Since MySQL 5.6, there have been some improvements in the index, such as the index condition pushdown (ICP), strictly speaking, the optimizer level improvement.

If it is simple to understand, that is, the optimizer will try to index the treatment from the Server layer to the storage engine layer. As an example, there is a table containing the combined index idx_cols containing (c1, c2, …, cn) n columns, if there is a range scan on the where1 condition, then the remaining c2, …, cn this n-1 On the index can not be used to extract and filter data, and ICP is to optimize this thing.

We are in the MySQL 5.6 environment to a simple test.

We create the table emp, which contains a primary key, a combined index to illustrate.

Create Table EMP (
EMPNO smallint The (. 5) unsigned Not null AUTO_INCREMENT,
ename VARCHAR (30) Not null,
DEPTNO smallint The (. 5) unsigned Not null,
Job VARCHAR (30) Not null,
Primary Key (EMPNO),
Key idx_emp_info (DEPTNO, Ename)
) engine = InnoDB charset = utf8;

Of course, I also randomly inserted a few data, meaning that

Insert the emp values ??(1, ‘zhangsan’, 1, ‘CEO’), (2, ‘lisi’, 2, ‘CFO’), (3, ‘wangwu’, 3, ‘CTO’), (4, Jeanron100 ‘, 3,’ Enginer ‘);

ICP control in the database parameters have an optimizer parameter optimizer_switch to unified management, I think this is the MySQL optimizer from our closest time. You can use the following way to view.

Show variables like ‘optimizer_switch’;

Of course, in the previous version of 5.6, you can not see the index condition pushdown this kind of words. The results in the 5.6 version are as follows:

In the same way as in the case of the following event: index_merge_switch = on, mrr = on, mrr_cost_based = on, block_nested_loop = on, batched_key_access = off, materialization = On, semijoin = on, loosescan = on, firstmatch = on, subquery_materialization_cost_based_ed, use_index_extensions = on Here we use two statements to compare the description, through the implementation of the plan to contrast.

Set optimizer_switch = “index_condition_pushdown = off”

> Explain select * from emp where deptno between 1 and 100 and ename = ‘jeanron100’;
+ —- + ————- + ——- + — — + ————— + —— + ——— + —— + —— +
| __- + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ —- + ——– —– + ——- + —— + ————— + —— + —— —
| + + + + + _— + | 1 | SIMPLE | emp | ALL | idx_emp_info | NULL | NULL | NULL | 4 | Using where |
+ —- + ————- + ——- + —— + ———– —- + —— + ——— + —— + —— + ————- +

And if it is turned on, see if ICP is enabled.
Set optimizer_switch = “index_condition_pushdown = on”;> explain select * from emp where deptno between 10 and 3000 and ename = ‘jeanron100’;

> Explain select * from emp where deptno between 1 and 300 and ename = ‘jeanron100’;
+ —- + ————- + ——- + — — + ————— + —— + ——— + —— + —— +
| __- + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ —- + ——– —– + ——- + —— + ————— + —— + —— —
| + + + + + _— + | 1 | SIMPLE | emp | ALL | idx_emp_info | NULL | NULL | NULL | 4 | Using where |
+ —- + ————- + ——- + —— + ———– —- + —— + ——— + —— + —— + ————- +
1 row in set (0.00 sec) This place is worth considering.