同一臺伺服器上面安裝多個mysql資料庫

wzq609發表於2017-09-07
伺服器MySql資料庫

【前言】測試環境中經常需要多臺mysql資料庫來進行各種環境和場景的模擬，由於測試環境中的資源一般都是比較有限的，也就需要在一臺伺服器上面搭建多個mysql資料庫來完成測試的需求。本文件介紹在Centos6.4的環境中安裝多個mysql 5.7資料庫的操作方法。

【1】mysql資料庫軟體的安裝

mysql資料庫軟體官方提供了三種的安裝檔案和方法，如下：

  • RPM方式安裝
  • 二進位制方式安裝
  • 原始碼編譯安裝

雖然原始碼安裝的方式比較麻煩，但是個人還是習慣用原始碼的方案安裝，mysql5.7的安裝跟早起5.5的安裝方式和步驟也幾乎一樣所以這裡就不再說明了。


【2】建立多個資料庫

軟體的安裝完成其實都是一樣的，就是建立資料庫的時候有點不一樣。

2.1 進行資料檔案目錄的規劃，本環境安裝了5個資料庫，在/data下面建立5個資料夾，並用埠號區分


點選(此處)摺疊或開啟

  1. [root@db01 data]# pwd
  2. /data
  3. [root@db01 data]# ll
  4. drwxr-xr-x. 6 mysql mysql 4096 7月 8 05:00 mysql3306
  5. drwxr-xr-x. 6 mysql mysql 4096 7月 20 21:46 mysql3307
  6. drwxr-xr-x. 7 mysql mysql 4096 7月 20 21:46 mysql3308
  7. drwxr-xr-x. 6 mysql mysql 4096 7月 20 21:46 mysql3309
  8. drwxr-xr-x. 5 mysql mysql 4096 7月 20 21:46 mysql3310
2.2 進行引數檔案的配置


點選(此處)摺疊或開啟

  1. [root@db01 data]# vi /etc/my.cnf

  3. [mysqld_multi]
  4. mysqld = /usr/local/mysql/bin/mysqld_safe
  5. #basedir = /usr/local/mysql
  6. mysqladmin = /usr/local/mysql/bin/mysqladmin
  7. user = mysql
  8. pass = mysql
  9. #password = mysql
  10. #bindir = /usr/local/mysql/bin

  12. [mysqld7]
  13. port = 3307
  14. socket = /tmp/mysql.sock7
  15. pid-file = /data/mysql3307/hostname.pid7
  16. datadir = /data/mysql3307
  17. user = mysql
  18. basedir = /usr/local/mysql
  19. log-bin = /data/mysql3307/bin-log
  20. server_id = 7
  21. gtid_mode = ON
  22. enforce-gtid-consistency=TRUE
  23. log_slave_updates= ON
  24. #skip-grant-tables

  26. [mysqld8]
  27. socket = /tmp/mysql.sock8
  28. port = 3308
  29. pid-file = /data/mysql3308/hostname.pid8
  30. datadir = /data/mysql3308
  31. user = mysql
  32. basedir = /usr/local/mysql
  33. #skip-grant-tables
  34. log-bin = /data/mysql3308/bin-log
  35. server_id=8
  36. gtid_mode=ON
  37. enforce-gtid-consistency=TRUE
  38. log_slave_updates= ON

  40. [mysqld9]
  41. socket = /tmp/mysql.sock9
  42. port = 3309
  43. pid-file = /data/mysql3309/hostname.pid9
  44. datadir = /data/mysql3309
  45. user = mysql
  46. basedir = /usr/local/mysql
  47. #skip-grant-tables
  48. log-bin = /data/mysql3309/bin-log
  49. server_id= 9
  50. gtid_mode=ON
  51. enforce-gtid-consistency=TRUE
  52. log_slave_updates= ON

  54. [mysqld10]
  55. socket = /tmp/mysql.sock10
  56. port = 3310
  57. pid-file = /data/mysql3310/hostname.pid10
  58. datadir = /data/mysql3310
  59. user = mysql
  60. basedir = /usr/local/mysql
  61. #skip-grant-tables
  62. log-bin = /data/mysql3310/bin-log
  63. server_id= 10
  64. gtid_mode=ON
  65. enforce-gtid-consistency=TRUE
  66. log_slave_updates= ON


2.3 建立資料庫，建立的過程中需要記錄資料庫的初始密碼

依次建立其他資料庫，記錄預設的隨機密碼

  • mysql3307的安裝

點選(此處)摺疊或開啟

  1. [root@db01 data]# /usr/local/mysql/bin/mysqld --initialize --datadir=/data/mysql3307
  2. 2017-07-20T14:31:01.890314Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
  3. 2017-07-20T14:31:09.081679Z 0 [Warning] InnoDB: New log files created, LSN=45790
  4. 2017-07-20T14:31:09.626403Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
  5. 2017-07-20T14:31:09.867983Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 12d65efe-6d58-11e7-9d39-000c29a755d3.
  6. 2017-07-20T14:31:09.873982Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
  7. 2017-07-20T14:31:09.923044Z 1 [Note] A temporary password is generated for root@localhost: wAQ*p>.O:4,p
  • mysql3308的安裝
  1. [root@db01 mysql3307]# cat auto.cnf
  2. [auto]
  3. server-uuid=12d65efe-6d58-11e7-9d39-000c29a755d3
  4. [root@db01 mysql3307]# /usr/local/mysql/bin/mysqld --initialize --datadir=/data/mysql3308
  5. 2017-07-20T14:32:36.027225Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
  6. 2017-07-20T14:32:38.601806Z 0 [Warning] InnoDB: New log files created, LSN=45790
  7. 2017-07-20T14:32:39.071963Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
  8. 2017-07-20T14:32:39.167438Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 48106897-6d58-11e7-a1b9-000c29a755d3.
  9. 2017-07-20T14:32:39.172770Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
  10. 2017-07-20T14:32:39.212540Z 1 [Note] A temporary password is generated for root@localhost: Ak3XwQpb=ta0
  • mysql3309的安裝
  1. [root@db01 mysql3307]# /usr/local/mysql/bin/mysqld --initialize --datadir=/data/mysql3309
  2. 2017-07-20T14:33:32.801680Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
  3. 2017-07-20T14:33:35.102950Z 0 [Warning] InnoDB: New log files created, LSN=45790
  4. 2017-07-20T14:33:35.443411Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
  5. 2017-07-20T14:33:35.557451Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 69acd736-6d58-11e7-a436-000c29a755d3.
  6. 2017-07-20T14:33:35.562713Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
  7. 2017-07-20T14:33:35.607109Z 1 [Note] A temporary password is generated for root@localhost: sDXL5hh61I>R
  • 資料庫mysql3310

點選(此處)摺疊或開啟

  1. [root@db01 mysql3307]# /usr/local/mysql/bin/mysqld --initialize --datadir=/data/mysql3310
  2. 2017-07-20T14:34:14.881243Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
  3. 2017-07-20T14:34:17.227399Z 0 [Warning] InnoDB: New log files created, LSN=45790
  4. 2017-07-20T14:34:17.744012Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
  5. 2017-07-20T14:34:17.904000Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 82ea694b-6d58-11e7-a566-000c29a755d3.
  6. 2017-07-20T14:34:17.908498Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
  7. 2017-07-20T14:34:17.923365Z 1 [Note] A temporary password is generated for root@localhost: Rq4*Teq#l;Ve

【3】修改資料庫的初始密碼

先啟動資料庫


  1. [root@db01 mysql3307]# /usr/local/mysql/bin/mysqld_multi start
  2. 修改預設密碼
  3. [root@db01 mysql3307]# mysqladmin -u root -p -P 3307 -S /tmp/mysql.sock7 password
  4. Enter password: 輸入預設密碼
  5. New password:
  6. Confirm new password:
  7. Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.


登入資料庫

[root@db01 mysql3307]# mysql -P 3307 -S /tmp/mysql.sock7 -u root –p


用預設的密碼登入會有以下的提示資訊：

點選(此處)摺疊或開啟

  1. mysql> show databases;
  2. ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

【4】設定mysqld_multi stop的關閉許可權

預設的情況下，不能透過mysqld_multi關閉資料庫，需要進行額外的設定，步驟如下：

建立mysql關閉的使用者
點選(此處)摺疊或開啟
  1. [root@db01 mysql3307]# mysql -u root -p -P 3310 -S /tmp/mysql.sock10
  2. Enter password:
  3. mysql> grant shutdown on *.* to 'mysql'@'localhost' identified by 'mysql';
  4. mysql> flush privileges
設定引數檔案的賬戶
點選(此處)摺疊或開啟
  1. [root@db01 mysql3307]# cat /etc/my.cnf
  2. [mysqld_multi]
  3. mysqld = /usr/local/mysql/bin/mysqld_safe
  4. #basedir = /usr/local/mysql
  5. mysqladmin = /usr/local/mysql/bin/mysqladmin
  6. user = mysql
  7. pass = mysql

透過以上的操作，便完成了在單臺伺服器上面安裝多個mysql資料庫的操作；

附加：常用的操作語句

/usr/local/mysql/bin/mysqld_multi start #啟動所有的資料庫

/usr/local/mysql/bin/mysqld_multi start 7 #啟動單臺資料庫

/usr/local/mysql/bin/mysqld_multi stop #關閉所有的資料庫

/usr/local/mysql/bin/mysqld_multi stop 7 #關閉單臺資料庫

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12679300/viewspace-2144619/，如需轉載，請註明出處，否則將追究法律責任。

相關文章