CentOS7下yum安裝mysql配置多實(shí)例

2019-11-08 20:39:40
字體：大中小
供稿：網(wǎng)友
最近想試試MySQL的主從復(fù)制功能“Mysql Replication”，但是苦于沒多臺(tái)電腦，于是考慮在linux上配置多個(gè)Mysql實(shí)例做為測(cè)試環(huán)境。
環(huán)境：虛擬機(jī)上的CentOS7
首先得在CentOS上安裝MySQL，下載源碼編譯太麻煩，于是考慮用yum直接安裝。由于在CentOS7上已經(jīng)沒有了MySQL的yum源，于是我們這次用MariaDB替換Mysql，畢竟兩者的差距不大，在使用上基本上是互通的。
yum安裝MariaDB：
yum install mariadb mariadb-server安裝完成后要進(jìn)行手動(dòng)開啟MySQL服務(wù)并初始化：
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!然后測(cè)試是否能夠正常登錄：
[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)]> 說明已經(jīng)能正常登錄了，我們可以查看MySQL的用戶權(quán)限了：
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)在這里我們可以看到有四個(gè)登錄賬號(hào)，user表示登錄名，host表示登錄主機(jī)限制，password為散列后的登錄密碼，其中%表示任意，如host的%表示可以任意主機(jī)登錄，在之后會(huì)寫到怎樣修改這些登錄數(shù)據(jù)。
現(xiàn)在我們退出MySQL登錄，準(zhǔn)備做MySQL上的多實(shí)例。
[root@localhost home]# mkdir /home/multiMysql[root@localhost home]# mkdir /home/multiMysql/{etc,socket,bin,datadir}在/home目錄下創(chuàng)建multiMysql文件夾，并在里面創(chuàng)建etc,socket,bin,datadir這四個(gè)文件夾備用。現(xiàn)在我們?cè)赿atadir中創(chuàng)建3個(gè)文件夾以放置三個(gè)實(shí)例的數(shù)據(jù)文件：3307,3308,3309
[root@localhost multiMysql]# mkdir /home/multiMysql/datadir/{3307,3308,3309}然后用mysql_install_db來生成即將使用的多個(gè)實(shí)例的數(shù)據(jù)文件，首先需要對(duì)/home/multiMysql進(jìn)行遞歸授權(quán)防止之后的操作出現(xiàn)權(quán)限不夠的情況：
chmod -R 777 /home/multiMysqlmysql_install_db --basedir=/usr --datadir=/home/multiMysql/datadir/3307 --user=mysqlmysql_install_db --basedir=/usr --datadir=/home/multiMysql/datadir/3308 --user=mysqlmysql_install_db --basedir=/usr --datadir=/home/multiMysql/datadir/3309 --user=mysql其中的參數(shù)--basedir是指mysql的二進(jìn)制文件目錄（誤？），--datadir是指即將安裝到的數(shù)據(jù)庫(kù)文件目錄，如果不知道--basedir該怎么填，可以登錄進(jìn)mysql后查詢：MariaDB [(none)]> show variables like '%basedir%';+---------------+-------+| Variable_name | Value |+---------------+-------+| basedir     | /usr |+---------------+-------+1 row in set (0.00 sec)--user是指mysql實(shí)例將使用的在linux系統(tǒng)中的用戶，最好命名為mysql，yum安裝后一般都有這個(gè)用戶，如果沒有可以自主創(chuàng)建：
groupadd mysqladduser -g mysql mysql現(xiàn)在來查看三份數(shù)據(jù)文件有沒有生成，例如查看3308的：[root@localhost multiMysql]# ls /home/multiMysql/datadir/3308/aria_log.00000001  aria_log_control  mysql  performance_schema  test如果里面有文件代表生成成功。接下來我們來做多實(shí)例的配置：
先創(chuàng)建一個(gè)公用配置文件：
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然后分別創(chuàng)建三個(gè)實(shí)例的配置文件：
[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復(fù)制兩份：
[root@localhost etc]# cp 3307.cnf 3308.cnf[root@localhost etc]# cp 3307.cnf 3309.cnf然后分別編輯復(fù)制的兩份配置文件，把端口和socket進(jìn)行修改：
[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是實(shí)例的端口，socket是實(shí)例運(yùn)行時(shí)的sock文件，datadir是之前我們生成的數(shù)據(jù)庫(kù)文件位置。
然后我們來編輯三個(gè)啟動(dòng)腳本：
[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因?yàn)槭莥um安裝，所以mysqld_safe和mysqladmin可以不用加路徑直接運(yùn)行，另外mysql_port是指這個(gè)bash簡(jiǎn)要打開的實(shí)例的端口，mysql_username和mysql_userpassword為我們即將在實(shí)例中配置的可關(guān)閉mysql進(jìn)程的mysql用戶名和密碼。現(xiàn)在給三個(gè)bash文件權(quán)限來執(zhí)行，并嘗試打開三個(gè)實(shí)例：
[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
先關(guān)閉yum安裝的默認(rèn)mysql實(shí)例進(jìn)程:
sudo service mariadb stop啟動(dòng)三個(gè)實(shí)例：
[root@localhost bin]# /home/multiMysql/bin/mysql3307 start[root@localhost bin]# /home/multiMysql/bin/mysql3308 start[root@localhost bin]# /home/multiMysql/bin/mysql3309 start查看是否有三個(gè)mysql進(jìn)程：
[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可以看到三個(gè)實(shí)例已經(jīng)啟動(dòng)，我們來嘗試連接三個(gè)實(shí)例的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可見三個(gè)實(shí)例已經(jīng)啟動(dòng)，可以進(jìn)每個(gè)實(shí)例后查看當(dāng)前實(shí)例的端口以確認(rèn)是否成功打開實(shí)例：
例如連接3307的sock后執(zhí)行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)可見確實(shí)已經(jīng)成功打開了。
下一步嘗試遠(yuǎn)程連接mysql實(shí)例：
查得虛擬機(jī)橋接的ip地址為：192.168.1.156
提示不允許遠(yuǎn)程連接，可知是權(quán)限不夠。在虛擬機(jī)中分別sock連接三個(gè)數(shù)據(jù)庫(kù)并創(chuàng)建用戶權(quán)限：
MariaDB [(none)]> grant all privileges on *.* to 'root'@'%' identified by 'poklau123' with grant option;MariaDB [(none)]> flush privileges;解釋一下這兩句sql的意思，grant是權(quán)限授予，all privileges表示授予所有權(quán)限，on表示授予的操作對(duì)象，*.*表示所有數(shù)據(jù)庫(kù)的所有表，to表示授予的對(duì)象‘root’@'%'表示任意主機(jī)的root登錄用戶，identified by 'poklau123'表示設(shè)置密碼為poklau123。flush privileges表示刷新用戶權(quán)限，不刷新的話權(quán)限是不會(huì)立刻生效的。
現(xiàn)在重新嘗試連接：
連接成功，三個(gè)實(shí)例都授予權(quán)限后三個(gè)實(shí)例都能分別連接了。
關(guān)閉實(shí)例：
[root@localhost bin]# /home/multiMysql/bin/mysql3307 stop[root@localhost bin]# /home/multiMysql/bin/mysql3308 stop[root@localhost bin]# /home/multiMysql/bin/mysql3309 stop然后查看進(jìn)程是否成功關(guān)閉：
[root@localhost bin]# ps -ef | grep mysqlroot      74999   3644  0 20:27 pts/0    00:00:00 grep --color=auto mysql可見三個(gè)實(shí)例已經(jīng)成功關(guān)閉，如果未能成功關(guān)閉，說明是bash文件中用戶名密碼對(duì)應(yīng)mysql實(shí)例里的用戶密碼權(quán)限不夠或錯(cuò)誤，調(diào)整權(quán)限即可。至此，三個(gè)mysql實(shí)例創(chuàng)建到此結(jié)束。