Mysql DBA Advanced Operations Learning Note-mysql Common Multi-instance configuration scenarios and multi-instance installation

This article for me to learn old boy teacher MySQL DBA senior operation of the study notes, rookie a If there is a wrong place to write, please the great God more guidance, I will thank you very much. hehe ~

7.6.1 MySQL Multi-instance common configuration scheme

6.1.1 Multi-instance configuration file deployment scenario

Implement multi-instance scenarios by configuring multiple configuration files and multiple startup programs

[[email protected] ~]# tree -L 2 /data//data/├── 3306│   ├── data│   ├── my.cnf│   └── mysql└── 3307├── data├── my.cnf└── mysql

Principle: Reduction of coupling

6.2 Installing a multi-instance MySQL database

6.2.1 Creating a MySQL multi-instance data file directory

The installation of MySQL has been written, it is no longer installed. Our data file directory uses the/data directory as the MySQL multi-instance total root directory, and then plan the different MySQL instance port number as/data below the level two directory, different port number is different instance directory to distinguish different instances, the level two directory contains MySQL data files, The directory for the configuration file and the startup file.

(1) Create the relevant MySQL multi-instance directory as follows

#####我们先把安装的单实例mysql停掉，要不然会冲突[[email protected] ~]# pkill mysqld#####然后把之前安装的单实例mysql的启动文件删掉，以后就不用这个方法启动了[[email protected] ~]# rm -f /etc/init.d/mysqld提示：多实例mysql的安装和单实例mysql的安装在make && make install之后就不同了。#####创建多实例目录，我们这里创建两个实例，如果想创建3个、4个同理往下增加即可。[[email protected] ~]# mkdir -p /data/{3306,33307}/data[[email protected] ~]# tree /data//data/? 总的多实例根目录├── 3306             3306实例的目录│   └── data     3306实例的数据文件目录└── 33307       3307实例的目录└── data            3307实例的数据文件目录4 directories, 0 files

6.2.2 Creating a MySQL Multi-instance configuration file

MySQL database By default provides users with multiple profile templates that users can select based on the size of the hardware configuration

[[email protected] mysql-5.5.32]# ls -l support-files/*.cnf-rw-r--r--. 1 root root  4723 1月  11 05:31 support-files/my-huge.cnf-rw-r--r--. 1 root root 19791 1月  11 05:31 support-files/my-innodb-heavy-4G.cnf-rw-r--r--. 1 root root  4697 1月  11 05:31 support-files/my-large.cnf-rw-r--r--. 1 root root  4708 1月  11 05:31 support-files/my-medium.cnf-rw-r--r--. 1 root root  2872 1月  11 05:31 support-files/my-small.cnf

Above is the default profile template for single-instance profiles, and configuring multiple instances is different in order to ensure that multiple instances are independent of each other. Therefore, we create a configuration file for each instance, a boot file, corresponding to its own data file.
In the actual work can take a modified template to make changes, through the RZ command and other ways to upload profile template my.cnf file.
We copy a database file to my.cnf in the MySQL installation package directory, and then empty the MY.CNF content to add the following content. The following is a 3306 configuration file, 3307 of the configuration file to replace the my.cnf 3306 to 3307, note that the Server-id value in 3307 must be modified not 3306 in the same server-id.

[[email protected] ~]# cp/home/zbf/mysql-5.5.32/support-files/my-small.cnf/data/3306/my.cnf[[email  Protected] 3306]# > my.cnf[[email protected] 3306]# vim my.cnf 1 [client] 2 port= 3306 3 socket =/data/3306/m Ysql.sock 4 [MySQL] 5 no-auto-rehash 6 [mysqld] 7 user= mysql 8 port= 3306 9 socket =/data/3306/mysql.sock Base dir =/usr/local/mysql DataDir =/data/3306/data open_files_limit= 1024x768 back_log = max_connections = 800 1  5 Max_connect_errors = Table_cache = 614 external-locking = FALSE Max_allowed_packet =8m = 1M Join_buffer_size = 1M thread_cache_size = thread_concurrency = 2 Query_cache_size = 2M Query_cach E_limit = 1M Query_cache_min_res_unit = 2k #default_table_type = InnoDB Thread_stack = 192K #transaction_isolatio n = read-committed tmp_table_size = 2M max_heap_table_size = 2M to Long_query_time = 1 #log_long_format #log-E Rror =/data/3306/error.log #log-slow-queries =/data/3306/slow.log Pid-file =/data/3306/mysql.pid Log-bin =/data/3306/mysql-bin Notoginseng relay-log =/d Ata/3306/relay-bin Relay-log-info-file =/data/3306/relay-log.info binlog_cache_size = 1M max_binlog_cache_size = 1M Max_binlog_size = 2M Expire_logs_days = 7 Key_buffer_size = 16M Read_buffer_size = 1M read_rnd_buffer_ Size = 1M Bulk_insert_buffer_size = 1M #InnoDB_sort_buffer_size = 1M #InnoDB_max_sort_file_size = 10G #InnoDB_ Max_extra_sort_file_size = 10G #InnoDB_repair_threads = 1 #InnoDB_recover lower_case_table_names = 1 skip-name  -resolve slave-skip-errors = 1032,1062 Replicate-ignore-db=mysql Server-id = 1 innodb_additional_mem_pool_size  = 4M Innodb_buffer_pool_size = 32M Innodb_data_file_path = Ibdata1:128m:autoextend Innodb_file_io_threads = 4 61 Innodb_thread_concurrency = 8 Innodb_flush_log_at_trx_commit = 2 Innodb_log_buffer_size = 2M Innodb_log_file_siz E = 4M Innodb_log_fileS_in_group = 3 innodb_max_dirty_pages_pct = innodb_lock_wait_timeout = innodb_file_per_table = 0 [MySQL] Dump] Max_allowed_packet = 2M [Mysqld_safe] Log-error=/data/3306/mysql_zbf3306.err pid-file=/data/3306/ Mysqld.pid

6.2.3 Creating a startup file

Here is the configuration file for the 3306 instance, configuring the configuration file for the 3307 instance to replace only 3306 of the 3306 instance configuration file with 3307.

#!/bin/bashcmdPath="/usr/local/mysql/bin"myPath="/data/3306"softPath="/usr/local/mysql"socketfile="$myPath/mysql.sock"my_user="root"my_pass="123456"  数据库设置的密码是多少这里就填多少start(){if [ ! -e "$socketfile" ];thenprintf "Starting MySQL......\n"/bin/sh   ${cmdPath}/mysqld_safe --defaults-file=${myPath}/my.cnf --user=mysql --basedir=${softPath} --datadir=${myPath}/data &>/dev/null &sleep 2   elseprintf "Mysqld is running....\n" && exit 1   fi}stop(){   if [ ! -e "$socketfile" ];thenprintf "MySQL is stopped...\n"        exit 1   else        printf "Stoping MySQL....\n"        mysqladmin -u"${my_user}" -p"${my_pass}"    -S "$socketfile" shutdown     fi}restart(){    printf "Restarting MySQL...\n"stop    sleep 2start}  case "$1" in   start)start   ;;   stop)stop   ;;   restart)restart   ;;   *)printf "Usage: $myPath/mysql {start|stop|restart}\n"exit 1esac

# # #最终如下

[[email protected] ~]# tree  /data/data├── 3306│   ├── data│   ├── my.cnf  3306实例的配置文件│   └── mysql       3306实例的启动文件└── 3307├── data├── my.cnf  3307实例的配置文件└── mysql   3307实例的启动文件

# # #多实例启动文件启动mysql服务实质

mysql_safe --defaults-file=/data/3306/my.cnf 2>&1 > /dev/null &mysql_safe --defaults-file=/data/3307/my.cnf 2>&1 > /dev/null &

# # #多实例启动文件的平滑停止mysql服务实质

mysqladmin -u root -p123456 -S /data/3306/mysql.sock shutdownmysqladmin -u root -p123456 -S /data/3307/mysql.sock shutdown

6.2.4 authorized users and groups to manage the entire multi-instance directory/data

[[email protected] ~]# chown -R mysql.mysql /data[[email protected] ~]# find /data -name mysql/data/3306/mysql/data/3307/mysql

6.2.5 Licensing MySQL Multi-instance service all startup files MySQL executable

[[email protected] ~]# find /data -type f -name "mysql"|xargs ls -l-rw-r--r--. 1 mysql mysql 1604 1月  12 17:06 /data/3306/mysql-rw-r--r--. 1 mysql mysql 1604 1月  12 17:06 /data/3307/mysql[[email protected] ~]# find /data -type f -name "mysql"|xargs chmod +x[[email protected] ~]# find /data -type f -name "mysql"|xargs ls -l-rwxr-xr-x. 1 mysql mysql 1604 1月  12 17:06 /data/3306/mysql-rwxr-xr-x. 1 mysql mysql 1604 1月  12 17:06 /data/3307/mysql

6.2.6 Configuring the MySQL command global use path

The front cmake installed MySQL has been equipped with, this is not worthy. With a full-board variable you can find it using the MySQL command.

[[email protected] ~]# tail -1 /etc/profileexport PATH=/usr/local/mysql/bin:$PATH

6.2.7 initializing a MySQL multi-instance database file

(1) Initialize command

# # #mysql5.1.X initialization command

mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/3307/data/ --user=mysqlmysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/3307/data/ --user=mysql

# # #mysql5.5.32 Initialize Command

[Email protected] ~]# cd/home/zbf/mysql-5.5.32/scripts/

To execute under scripts, not under MySQL bin. Note to execute Add./in the current directory.

./mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/3306/data/ --user=mysql./mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/3307/data/ --user=mysql

(2) Why do you want to initialize the database?

A. The primary purpose of initialization is to create a base database file, such as a MySQL library table.

B. After initialization, you can view the corresponding instance database directory and see the following files.

6.2.8 start MySQL Service

/data/3306/mysql start/data/3307/mysql start

6.2.9 MySQL fault troubleshooting multi-instance Database

If MySQL does not get up, troubleshoot the method as follows

(1) If you find that the MySQL port is not displayed, wait a few seconds for MySQL service to start up a bit slowly.

(2) If not, please check the error log, the error log path under the MY.CNF profile path.
grep log-error/data/3306/mysql_zbf3306.err|tail-1

(3) Take a closer look at the output returned by all execution commands, and do not ignore the key output results.

(4) Check the/var/log/messages of the system.

(5) If it is a related service, check the log of the relevant service at the same time.

6.2.10 MySQL Multi-instance Login

After the MySQL installation is complete, by default the administrator account root is no password. Logging in to a different MySQL database requires specifying the sock path for the different libraries. If it is remote, specify the IP address.

6.3 MySQL Security Configuration

6.3.1 Add password for root

mysqladmin -u root -S /data/3306/mysql.sock password ‘123456‘mysqladmin -u root -S /data/3307/mysql.sock password ‘123456‘

# # #设置完数据路密码登录数据库的方法

[[email protected] 3306]# mysql -uroot -p123456 -S /data/3306/mysql.sock[[email protected] 3306]# mysql -uroot -p123456 -S /data/3307/mysql.sock

6.3.2 Modify execute permissions for MySQL multi-instance startup scripts, allowing only root users to have Execute permissions

[[email protected] ~]# find /data -type f -name "mysql" -exec ls -l {} \;-rwxr-xr-x 1 mysql mysql 1252 1月  15 02:18 /data/3306/mysql-rwxr-xr-x 1 mysql mysql 1252 1月  15 04:45 /data/3307/mysql[[email protected] ~]# find /data -type f -name "mysql" -exec chmod 700 {} \;[[email protected] ~]# find /data -type f -name "mysql" -exec chown 700 {} \;[[email protected] ~]# find /data -type f -name "mysql" -exec ls -l {} \;-rwx------ 1 root root 1252 1月  15 02:18 /data/3306/mysql-rwx------ 1 root root 1252 1月  15 04:45 /data/3307/mysql

Mysql DBA Advanced Operations Learning Note-mysql Common Multi-instance configuration scenarios and multi-instance installation