CentOS7下yum安装mysql配置多实例

最近想试试Mysql的主从复制功能“Mysql Replication”，但是苦于没多台电脑，于是考虑在Linux上配置多个Mysql实例做为测试环境。

环境：虚拟机上的CentOS7

首先得在CentOS上安装MySQL，下载源码编译太麻烦，于是考虑用yum直接安装。由于在CentOS7上已经没有了MySQL的yum源，于是我们这次用MariaDB替换Mysql，毕竟两者的差距不大，在使用上基本上是互通的。

yum安装MariaDB：

yum install mariadb mariadb-server

安装完成后要进行手动开启MySQL服务并初始化：

service mariadb start

[root@localhost ~]# mysql_secure_installationNOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!In order to log into MariaDB to secure it, we'll need the currentpassword for the root user.  If you've just installed MariaDB, andyou haven't set the root password yet, the password will be blank,so you should just press enter here.Enter current password for root (enter for none): OK, successfully used password, moving on...Setting the root password ensures that nobody can log into the MariaDBroot user without the proper authorisation.Set root password? [Y/n] YNew password: Re-enter new password: Password updated successfully!Reloading privilege tables.. ... Success!By default, a MariaDB installation has an anonymous user, allowing anyoneto log into MariaDB without having to have a user account created forthem.  This is intended only for testing, and to make the installationgo a bit smoother.  You should remove them before moving into aproduction environment.Remove anonymous users? [Y/n] Y ... Success!Normally, root should only be allowed to connect from 'localhost'.  Thisensures that someone cannot guess at the root password from the network.Disallow root login remotely? [Y/n] n ... skipping.By default, MariaDB comes with a database named 'test' that anyone canaccess.  This is also intended only for testing, and should be removedbefore moving into a production environment.Remove test database and access to it? [Y/n] n ... skipping.Reloading the privilege tables will ensure that all changes made so farwill take effect immediately.Reload privilege tables now? [Y/n] Y ... Success!Cleaning up...All done!  If you've completed all of the above steps, your MariaDBinstallation should now be secure.Thanks for using MariaDB!

然后测试是否能够正常登录：

[root@localhost ~]# mysql -u root -pEnter password: Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 7Server version: 5.5.52-MariaDB MariaDB ServerCopyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> 

说明已经能正常登录了，我们可以查看MySQL的用户权限了：

MariaDB [(none)]> select user,host,password from mysql.user \G;*************************** 1. row ***************************    user: root    host: localhostpassword: *FC3D158275832C2A64C4A6ECD9154A703179BF8F*************************** 2. row ***************************    user: root    host: 127.0.0.1password: *FC3D158275832C2A64C4A6ECD9154A703179BF8F*************************** 3. row ***************************    user: root    host: ::1password: *FC3D158275832C2A64C4A6ECD9154A703179BF8F*************************** 4. row ***************************    user: root    host: %password: *FC3D158275832C2A64C4A6ECD9154A703179BF8F4 rows in set (0.01 sec)

在这里我们可以看到有四个登录账号，user表示登录名，host表示登录主机限制，password为散列后的登录密码，其中%表示任意，如host的%表示可以任意主机登录，在之后会写到怎样修改这些登录数据。

现在我们退出MySQL登录，准备做MySQL上的多实例。

[root@localhost home]# mkdir /home/multiMysql[root@localhost home]# mkdir /home/multiMysql/{etc,socket,bin,datadir}

在/home目录下创建multiMysql文件夹，并在里面创建etc,socket,bin,datadir这四个文件夹备用。
现在我们在datadir中创建3个文件夹以放置三个实例的数据文件：3307,3308,3309

[root@localhost multiMysql]# mkdir /home/multiMysql/datadir/{3307,3308,3309}

然后用mysql_install_db来生成即将使用的多个实例的数据文件，首先需要对/home/multiMysql进行递归授权防止之后的操作出现权限不够的情况：

chmod -R 777 /home/multiMysql

mysql_install_db --basedir=/usr --datadir=/home/multiMysql/datadir/3307 --user=mysql

mysql_install_db --basedir=/usr --datadir=/home/multiMysql/datadir/3308 --user=mysql

mysql_install_db --basedir=/usr --datadir=/home/multiMysql/datadir/3309 --user=mysql

其中的参数--basedir是指mysql的二进制文件目录（误？），--datadir是指即将安装到的数据库文件目录，如果不知道--basedir该怎么填，可以登录进mysql后查询：

MariaDB [(none)]> show variables like '%basedir%';+---------------+-------+| Variable_name | Value |+---------------+-------+| basedir     | /usr |+---------------+-------+1 row in set (0.00 sec)

--user是指mysql实例将使用的在linux系统中的用户，最好命名为mysql，yum安装后一般都有这个用户，如果没有可以自主创建：

groupadd mysqladduser -g mysql mysql

现在来查看三份数据文件有没有生成，例如查看3308的：

[root@localhost multiMysql]# ls /home/multiMysql/datadir/3308/aria_log.00000001  aria_log_control  mysql  performance_schema  test

如果里面有文件代表生成成功。

接下来我们来做多实例的配置：

先创建一个公用配置文件：

mkdir /home/multiMysql/etc/my.cnf.d/

vim /home/multiMysql/etc/my.cnf.d/my.cnf

[mysqld]skip-name-resolvelower_case_table_names=1innodb_file_per_table=1back_log = 50max_connections = 300max_connect_errors = 1000table_open_cache = 2048max_allowed_packet = 16Mbinlog_cache_size = 2Mmax_heap_table_size = 64Msort_buffer_size = 2Mjoin_buffer_size = 2Mthread_cache_size = 64thread_concurrency = 8query_cache_size = 64Mquery_cache_limit = 2Mft_min_word_len = 4default-storage-engine = innodbthread_stack = 192Ktransaction_isolation = REPEATABLE-READtmp_table_size = 64Mlog-bin=mysql-binbinlog_format=mixedslow_query_loglong_query_time = 1server-id = 1key_buffer_size = 8Mread_buffer_size = 2Mread_rnd_buffer_size = 2Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1myisam_recoverinnodb_additional_mem_pool_size = 16Minnodb_buffer_pool_size = 200Minnodb_data_file_path = ibdata1:10M:autoextendinnodb_file_io_threads = 8innodb_thread_concurrency = 16innodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 16Minnodb_log_file_size = 512Minnodb_log_files_in_group = 3innodb_max_dirty_pages_pct = 60innodb_lock_wait_timeout = 120[mysqldump]quickmax_allowed_packet = 256M[mysql]no-auto-rehashprompt=\\u@\\d \\R:\\m>[myisamchk]key_buffer_size = 512Msort_buffer_size = 512Mread_buffer = 8Mwrite_buffer = 8M[mysqlhotcopy]interactive-timeout[mysqld_safe]open-files-limit = 8192

然后分别创建三个实例的配置文件：

[root@localhost etc]# vim 3307.cnf 

[client]port = 3307socket = /home/multiMysql/socket/mysql3307.sock[mysqld]datadir=/home/multiMysql/datadir/3307port = 3307socket = /home/multiMysql/socket/mysql3307.sock!includedir /home/multiMysql/etc/my.cnf.d

然后把3307.cnf复制两份：

[root@localhost etc]# cp 3307.cnf 3308.cnf[root@localhost etc]# cp 3307.cnf 3309.cnf

然后分别编辑复制的两份配置文件，把端口和socket进行修改：

[root@localhost etc]# vim 3308.cnf 

[client]port = 3308socket = /home/multiMysql/socket/mysql3308.sock[mysqld]datadir=/home/multiMysql/datadir/3308port = 3308socket = /home/multiMysql/socket/mysql3308.sock!includedir /home/multiMysql/etc/my.cnf.d

[root@localhost etc]# vim 3309.cnf 

[client]port = 3309socket = /home/multiMysql/socket/mysql3309.sock[mysqld]datadir=/home/multiMysql/datadir/3309port = 3309socket = /home/multiMysql/socket/mysql3309.sock!includedir /home/multiMysql/etc/my.cnf.d

在配置文件中，port是实例的端口，socket是实例运行时的sock文件，datadir是之前我们生成的数据库文件位置。

然后我们来编辑三个启动脚本：

[root@localhost bin]# vim /home/multiMysql/bin/mysql3307

#!/bin/bashmysql_port=3307mysql_username="root"mysql_password=""function_start_mysql(){printf "Starting MySQL...\n"mysqld_safe --defaults-file=/home/multiMysql/etc/${mysql_port}.cnf 2>&1 > /dev/null &}function_stop_mysql(){printf "Stoping MySQL...\n"mysqladmin -u ${mysql_username} -p${mysql_password} -S /home/multiMysql/socket/mysql${mysql_port}.sock shutdown}function_restart_mysql(){printf "Restarting MySQL...\n"function_stop_mysqlfunction_start_mysql}function_kill_mysql(){kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')}case $1 instart)function_start_mysql;;stop)function_stop_mysql;;kill)function_kill_mysql;;restart)function_stop_mysqlfunction_start_mysql;;*)echo 

[root@localhost bin]# vim /home/multiMysql/bin/mysql3308

#!/bin/bashmysql_port=3308mysql_username="root"mysql_password=""function_start_mysql(){printf "Starting MySQL...\n"mysqld_safe --defaults-file=/home/multiMysql/etc/${mysql_port}.cnf 2>&1 > /dev/null &}function_stop_mysql(){printf "Stoping MySQL...\n"mysqladmin -u ${mysql_username} -p${mysql_password} -S /home/multiMysql/socket/mysql${mysql_port}.sock shutdown}function_restart_mysql(){printf "Restarting MySQL...\n"function_stop_mysqlfunction_start_mysql}function_kill_mysql(){kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')}case $1 instart)function_start_mysql;;stop)function_stop_mysql;;kill)function_kill_mysql;;restart)function_stop_mysqlfunction_start_mysql;;*)echo "Usage: /data/dbdata_${mysql_port}/mysqld {start|stop|restart|kill}";;esac

[root@localhost bin]# vim /home/multiMysql/bin/mysql3309

#!/bin/bashmysql_port=3309mysql_username="root"mysql_password=""function_start_mysql(){printf "Starting MySQL...\n"mysqld_safe --defaults-file=/home/multiMysql/etc/${mysql_port}.cnf 2>&1 > /dev/null &}function_stop_mysql(){printf "Stoping MySQL...\n"mysqladmin -u ${mysql_username} -p${mysql_password} -S /home/multiMysql/socket/mysql${mysql_port}.sock shutdown}function_restart_mysql(){printf "Restarting MySQL...\n"function_stop_mysqlfunction_start_mysql}function_kill_mysql(){kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')}case $1 instart)function_start_mysql;;stop)function_stop_mysql;;kill)function_kill_mysql;;restart)function_stop_mysqlfunction_start_mysql;;*)echo "Usage: /data/dbdata_${mysql_port}/mysqld {start|stop|restart|kill}";;esac

因为是yum安装，所以mysqld_safe和mysqladmin可以不用加路径直接运行，另外mysql_port是指这个bash简要打开的实例的端口，mysql_username和mysql_userpassword为我们即将在实例中配置的可关闭mysql进程的mysql用户名和密码。

现在给三个bash文件权限来执行，并尝试打开三个实例：

[root@localhost bin]# chmod 777 /home/multiMysql/bin/mysql3307[root@localhost bin]# chmod 777 /home/multiMysql/bin/mysql3308[root@localhost bin]# chmod 777 /home/multiMysql/bin/mysql3309

先关闭yum安装的默认mysql实例进程:

sudo service mariadb stop

启动三个实例：

[root@localhost bin]# /home/multiMysql/bin/mysql3307 start[root@localhost bin]# /home/multiMysql/bin/mysql3308 start[root@localhost bin]# /home/multiMysql/bin/mysql3309 start

查看是否有三个mysql进程：

[root@localhost bin]# ps -ef | grep mysqlroot      47013      1  0 19:57 pts/0    00:00:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/home/multiMysql/etc/3307.cnfmysql     47680  47013  2 19:57 pts/0    00:00:04 /usr/libexec/mysqld --defaults-file=/home/multiMysql/etc/3307.cnf --basedir=/usr --datadir=/home/multiMysql/datadir/3307 --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/home/multiMysql/datadir/3307/localhost.localdomain.err --open-files-limit=8192 --pid-file=localhost.localdomain.pid --socket=/home/multiMysql/socket/mysql3307.sock --port=3307root      50504      1  0 20:00 pts/0    00:00:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/home/multiMysql/etc/3308.cnfmysql     51183  50504  9 20:00 pts/0    00:00:03 /usr/libexec/mysqld --defaults-file=/home/multiMysql/etc/3308.cnf --basedir=/usr --datadir=/home/multiMysql/datadir/3308 --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/home/multiMysql/datadir/3308/localhost.localdomain.err --open-files-limit=8192 --pid-file=localhost.localdomain.pid --socket=/home/multiMysql/socket/mysql3308.sock --port=3308root      51224      1  0 20:00 pts/0    00:00:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/home/multiMysql/etc/3309.cnfmysql     51952  51224  2 20:00 pts/0    00:00:00 /usr/libexec/mysqld --defaults-file=/home/multiMysql/etc/3309.cnf --basedir=/usr --datadir=/home/multiMysql/datadir/3309 --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/home/multiMysql/datadir/3309/localhost.localdomain.err --open-files-limit=8192 --pid-file=localhost.localdomain.pid --socket=/home/multiMysql/socket/mysql3309.sock --port=3309root      52445   3644  0 20:01 pts/0    00:00:00 grep --color=auto mysql

可以看到三个实例已经启动，我们来尝试连接三个实例的sock：

[root@localhost bin]# mysql -u root -S /home/multiMysql/socket/mysql3307.sock Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 1Server version: 5.5.52-MariaDB MariaDB ServerCopyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> quitBye[root@localhost bin]# mysql -u root -S /home/multiMysql/socket/mysql3308.sock Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 1Server version: 5.5.52-MariaDB MariaDB ServerCopyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> quitBye[root@localhost bin]# mysql -u root -S /home/multiMysql/socket/mysql3309.sock Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 1Server version: 5.5.52-MariaDB MariaDB ServerCopyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> quitBye

可见三个实例已经启动，可以进每个实例后查看当前实例的端口以确认是否成功打开实例：

例如连接3307的sock后执行sql：

MariaDB [(none)]> show variables like '%port%';+-------------------------------------+-------+| Variable_name                       | Value |+-------------------------------------+-------+| extra_port                          | 0     || innodb_import_table_from_xtrabackup | 0     || innodb_support_xa                   | ON    || large_files_support                 | ON    || port                                | 3307  || progress_report_time                | 5     || report_host                         |       || report_password                     |       || report_port                         | 3307  || report_user                         |       |+-------------------------------------+-------+10 rows in set (0.00 sec)

可见确实已经成功打开了。

下一步尝试远程连接mysql实例：

查得虚拟机桥接的ip地址为：192.168.1.156

提示不允许远程连接，可知是权限不够。在虚拟机中分别sock连接三个数据库并创建用户权限：

MariaDB [(none)]> grant all privileges on *.* to 'root'@'%' identified by 'poklau123' with grant option;MariaDB [(none)]> flush privileges;

解释一下这两句sql的意思，grant是权限授予，all privileges表示授予所有权限，on表示授予的操作对象，*.*表示所有数据库的所有表，to表示授予的对象‘root’@'%'表示任意主机的root登录用户，identified by 'poklau123'表示设置密码为poklau123。flush privileges表示刷新用户权限，不刷新的话权限是不会立刻生效的。

现在重新尝试连接：

连接成功，三个实例都授予权限后三个实例都能分别连接了。

关闭实例：

[root@localhost bin]# /home/multiMysql/bin/mysql3307 stop[root@localhost bin]# /home/multiMysql/bin/mysql3308 stop[root@localhost bin]# /home/multiMysql/bin/mysql3309 stop

然后查看进程是否成功关闭：

[root@localhost bin]# ps -ef | grep mysqlroot      74999   3644  0 20:27 pts/0    00:00:00 grep --color=auto mysql

可见三个实例已经成功关闭，如果未能成功关闭，说明是bash文件中用户名密码对应mysql实例里的用户密码权限不够或错误，调整权限即可。至此，三个mysql实例创建到此结束。