MySQL多实例安装
建立MySQL账号
[root@localhost ~]# groupadd mysql [root@localhost ~]# useradd -s /sbin/nologin -g mysql -M mysql # -s /sbin/nologin 禁止该用户登陆 # -g mysql 指定属于mysql组 # -M 表示不创建用户家目录
安装mysql软件
环境部署
mysql-5.6.22-linux-glibc2.5-x86_64.tar.gz CentOS6.9
[root@localhost ~]# mkdir /home/software [root@localhost ~]# cd /home/software/ [root@localhost software]# ls mysql-5.6.22-linux-glibc2.5-x86_64.tar.gz
解压和迁移
[root@localhost software]# ls mysql-5.6.22-linux-glibc2.5-x86_64 mysql-5.6.22-linux-glibc2.5-x86_64.tar.gz [root@localhost software]# mv mysql-5.6.22-linux-glibc2.5-x86_64 /usr/local/mysql [root@localhost software]# cd /usr/local/mysql/ [root@localhost mysql]# ls bin COPYING data docs include INSTALL-BINARY lib man mysql-test README scripts share sql-bench support-files
创建相关目录
[root@localhost ~]# mkdir -p /data/mysql/{mysql_3306,mysql_3307} [root@localhost ~]# mkdir /data/mysql/mysql_3306/{data,log,tmp} [root@localhost ~]# mkdir /data/mysql/mysql_3307/{data,log,tmp}
更改目录权限
[root@localhost ~]# chown -R mysql:mysql /data/mysql/ [root@localhost ~]# chown -R mysql:mysql /usr/local/mysql/
添加环境变量
[root@localhost ~]# echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile [root@localhost ~]# source /etc/profile
复制my.cnf文件到etc目录
[root@localhost ~]# cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf cp: overwrite `/etc/my.cnf'? y
修改my.cnf
[root@localhost ~]# vi /etc/my.cnf [root@localhost ~]#
# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html # *** DO NOT EDIT THIS FILE. It's a template which will be copied to the # *** default location during install, and will be replaced if you # *** upgrade to a newer version of MySQL. [client] port=3306 socket=/tmp/mysql.sock [mysqld_multi] mysqld = /usr/local/mysql /bin/mysqld_safe mysqladmin = /usr/local/mysql /bin/mysqladmin log = /data/mysql/mysqld_multi.log [mysqld] user=mysql basedir = /usr/local/mysql sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES explicit_defaults_for_timestamp=true [mysqld3306] mysqld=mysqld mysqladmin=mysqladmin datadir=/data/mysql/mysql_3306/data port=3306 server_id=3306 socket=/tmp/mysql_3306.sock log-output=file slow_query_log = 1 long_query_time = 1 slow_query_log_file = /data/mysql/mysql_3306/log/slow.log log-error = /data/mysql/mysql_3306/log/error.log binlog_format = mixed log-bin = /data/mysql/mysql_3306/log/mysql3306_bin [mysqld3307] mysqld=mysqld mysqladmin=mysqladmin datadir=/data/mysql/mysql_3307/data port=3307 server_id=3307 socket=/tmp/mysql_3307.sock log-output=file slow_query_log = 1 long_query_time = 1 slow_query_log_file = /data/mysql/mysql_3307/log/slow.log log-error = /data/mysql/mysql_3307/log/error.log binlog_format = mixed log-bin = /data/mysql/mysql_3307/log/mysql3307_bin # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. # basedir = ..... # datadir = ..... # port = ..... # server_id = ..... # socket = ..... # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
初始化数据库
初始化3306数据库
[root@localhost ~]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3306/data --defaults-file=/etc/my.cnf
初始化后显示
[root@localhost ~]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3306/data --defaults-file=/etc/my.cnf Installing MySQL system tables...2018-11-27 00:36:44 3144 [Note] InnoDB: Using atomics to ref count buffer pool pages 2018-11-27 00:36:44 3144 [Note] InnoDB: The InnoDB memory heap is disabled 2018-11-27 00:36:44 3144 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2018-11-27 00:36:44 3144 [Note] InnoDB: Memory barrier is not used 2018-11-27 00:36:44 3144 [Note] InnoDB: Compressed tables use zlib 1.2.3 2018-11-27 00:36:44 3144 [Note] InnoDB: Using Linux native AIO 2018-11-27 00:36:44 3144 [Note] InnoDB: Using CPU crc32 instructions 2018-11-27 00:36:44 3144 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2018-11-27 00:36:45 3144 [Note] InnoDB: Completed initialization of buffer pool 2018-11-27 00:36:45 3144 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created! 2018-11-27 00:36:45 3144 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB 2018-11-27 00:36:45 3144 [Note] InnoDB: Database physically writes the file full: wait... 2018-11-27 00:36:45 3144 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB 2018-11-27 00:36:45 3144 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB 2018-11-27 00:36:46 3144 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 2018-11-27 00:36:46 3144 [Warning] InnoDB: New log files created, LSN=45781 2018-11-27 00:36:46 3144 [Note] InnoDB: Doublewrite buffer not found: creating new 2018-11-27 00:36:46 3144 [Note] InnoDB: Doublewrite buffer created 2018-11-27 00:36:46 3144 [Note] InnoDB: 128 rollback segment(s) are active. 2018-11-27 00:36:46 3144 [Warning] InnoDB: Creating foreign key constraint system tables. 2018-11-27 00:36:46 3144 [Note] InnoDB: Foreign key constraint system tables created 2018-11-27 00:36:46 3144 [Note] InnoDB: Creating tablespace and datafile system tables. 2018-11-27 00:36:46 3144 [Note] InnoDB: Tablespace and datafile system tables created. 2018-11-27 00:36:46 3144 [Note] InnoDB: Waiting for purge to start 2018-11-27 00:36:46 3144 [Note] InnoDB: 5.6.22 started; log sequence number 0 2018-11-27 00:36:47 3144 [Note] Binlog end 2018-11-27 00:36:47 3144 [Note] InnoDB: FTS optimize thread exiting. 2018-11-27 00:36:47 3144 [Note] InnoDB: Starting shutdown... 2018-11-27 00:36:48 3144 [Note] InnoDB: Shutdown completed; log sequence number 1625977 OK Filling help tables...2018-11-27 00:36:48 3168 [Note] InnoDB: Using atomics to ref count buffer pool pages 2018-11-27 00:36:48 3168 [Note] InnoDB: The InnoDB memory heap is disabled 2018-11-27 00:36:48 3168 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2018-11-27 00:36:48 3168 [Note] InnoDB: Memory barrier is not used 2018-11-27 00:36:48 3168 [Note] InnoDB: Compressed tables use zlib 1.2.3 2018-11-27 00:36:48 3168 [Note] InnoDB: Using Linux native AIO 2018-11-27 00:36:48 3168 [Note] InnoDB: Using CPU crc32 instructions 2018-11-27 00:36:48 3168 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2018-11-27 00:36:48 3168 [Note] InnoDB: Completed initialization of buffer pool 2018-11-27 00:36:48 3168 [Note] InnoDB: Highest supported file format is Barracuda. 2018-11-27 00:36:48 3168 [Note] InnoDB: 128 rollback segment(s) are active. 2018-11-27 00:36:48 3168 [Note] InnoDB: Waiting for purge to start 2018-11-27 00:36:48 3168 [Note] InnoDB: 5.6.22 started; log sequence number 1625977 2018-11-27 00:36:48 3168 [Note] Binlog end 2018-11-27 00:36:48 3168 [Note] InnoDB: FTS optimize thread exiting. 2018-11-27 00:36:48 3168 [Note] InnoDB: Starting shutdown... 2018-11-27 00:36:50 3168 [Note] InnoDB: Shutdown completed; log sequence number 1625987 OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/local/mysql//bin/mysqladmin -u root password 'new-password' /usr/local/mysql//bin/mysqladmin -u root -h localhost.localdomain password 'new-password' Alternatively you can run: /usr/local/mysql//bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd . ; /usr/local/mysql//bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd mysql-test ; perl mysql-test-run.pl Please report any problems at http://bugs.mysql.com/ The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at http://shop.mysql.com New default config file was created as /usr/local/mysql//my.cnf and will be used by default by the server when you start it. You may edit this file to change server settings WARNING: Default config file /etc/my.cnf exists on the system This file will be read by default by the MySQL server If you do not want to use this, either remove it, or use the --defaults-file argument to mysqld_safe when starting the server
初始化3307数据库
[root@localhost ~]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3307/data --defaults-file=/etc/my.cnf
初始化后显示
[root@localhost ~]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3307/data --defaults-file=/etc/my.cnf Installing MySQL system tables...2018-11-27 00:38:40 3196 [Note] InnoDB: Using atomics to ref count buffer pool pages 2018-11-27 00:38:40 3196 [Note] InnoDB: The InnoDB memory heap is disabled 2018-11-27 00:38:40 3196 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2018-11-27 00:38:40 3196 [Note] InnoDB: Memory barrier is not used 2018-11-27 00:38:40 3196 [Note] InnoDB: Compressed tables use zlib 1.2.3 2018-11-27 00:38:40 3196 [Note] InnoDB: Using Linux native AIO 2018-11-27 00:38:40 3196 [Note] InnoDB: Using CPU crc32 instructions 2018-11-27 00:38:40 3196 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2018-11-27 00:38:40 3196 [Note] InnoDB: Completed initialization of buffer pool 2018-11-27 00:38:40 3196 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created! 2018-11-27 00:38:40 3196 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB 2018-11-27 00:38:40 3196 [Note] InnoDB: Database physically writes the file full: wait... 2018-11-27 00:38:40 3196 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB 2018-11-27 00:38:41 3196 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB 2018-11-27 00:38:42 3196 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 2018-11-27 00:38:42 3196 [Warning] InnoDB: New log files created, LSN=45781 2018-11-27 00:38:42 3196 [Note] InnoDB: Doublewrite buffer not found: creating new 2018-11-27 00:38:42 3196 [Note] InnoDB: Doublewrite buffer created 2018-11-27 00:38:42 3196 [Note] InnoDB: 128 rollback segment(s) are active. 2018-11-27 00:38:42 3196 [Warning] InnoDB: Creating foreign key constraint system tables. 2018-11-27 00:38:42 3196 [Note] InnoDB: Foreign key constraint system tables created 2018-11-27 00:38:42 3196 [Note] InnoDB: Creating tablespace and datafile system tables. 2018-11-27 00:38:42 3196 [Note] InnoDB: Tablespace and datafile system tables created. 2018-11-27 00:38:42 3196 [Note] InnoDB: Waiting for purge to start 2018-11-27 00:38:42 3196 [Note] InnoDB: 5.6.22 started; log sequence number 0 2018-11-27 00:38:43 3196 [Note] Binlog end 2018-11-27 00:38:43 3196 [Note] InnoDB: FTS optimize thread exiting. 2018-11-27 00:38:43 3196 [Note] InnoDB: Starting shutdown... 2018-11-27 00:38:44 3196 [Note] InnoDB: Shutdown completed; log sequence number 1625977 OK Filling help tables...2018-11-27 00:38:44 3218 [Note] InnoDB: Using atomics to ref count buffer pool pages 2018-11-27 00:38:44 3218 [Note] InnoDB: The InnoDB memory heap is disabled 2018-11-27 00:38:44 3218 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2018-11-27 00:38:44 3218 [Note] InnoDB: Memory barrier is not used 2018-11-27 00:38:44 3218 [Note] InnoDB: Compressed tables use zlib 1.2.3 2018-11-27 00:38:44 3218 [Note] InnoDB: Using Linux native AIO 2018-11-27 00:38:44 3218 [Note] InnoDB: Using CPU crc32 instructions 2018-11-27 00:38:44 3218 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2018-11-27 00:38:44 3218 [Note] InnoDB: Completed initialization of buffer pool 2018-11-27 00:38:44 3218 [Note] InnoDB: Highest supported file format is Barracuda. 2018-11-27 00:38:44 3218 [Note] InnoDB: 128 rollback segment(s) are active. 2018-11-27 00:38:44 3218 [Note] InnoDB: Waiting for purge to start 2018-11-27 00:38:44 3218 [Note] InnoDB: 5.6.22 started; log sequence number 1625977 2018-11-27 00:38:44 3218 [Note] Binlog end 2018-11-27 00:38:44 3218 [Note] InnoDB: FTS optimize thread exiting. 2018-11-27 00:38:44 3218 [Note] InnoDB: Starting shutdown... 2018-11-27 00:38:46 3218 [Note] InnoDB: Shutdown completed; log sequence number 1625987 OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /usr/local/mysql//bin/mysqladmin -u root password 'new-password' /usr/local/mysql//bin/mysqladmin -u root -h localhost.localdomain password 'new-password' Alternatively you can run: /usr/local/mysql//bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd . ; /usr/local/mysql//bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd mysql-test ; perl mysql-test-run.pl Please report any problems at http://bugs.mysql.com/ The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at http://shop.mysql.com WARNING: Found existing config file /usr/local/mysql//my.cnf on the system. Because this file might be in use, it was not replaced, but was used in bootstrap (unless you used --defaults-file) and when you later start the server. The new default config file was created as /usr/local/mysql//my-new.cnf, please compare it with your file and take the changes you need. WARNING: Default config file /etc/my.cnf exists on the system This file will be read by default by the MySQL server If you do not want to use this, either remove it, or use the --defaults-file argument to mysqld_safe when starting the server
查看初始化后结果
[root@localhost ~]# cd /data/mysql/mysql_3306/data [root@localhost data]# ls ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema test [root@localhost data]# cd /data/mysql/mysql_3307/data [root@localhost data]# ls ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema test
设置启动文件
[root@localhost data]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql [root@localhost data]#
mysqld_multi进行多实例管理
# 启动全部实例 [root@localhost data]# /usr/local/mysql/bin/mysqld_multi start # 查看全部实例状态 [root@localhost data]# /usr/local/mysql/bin/mysqld_multi report Reporting MySQL servers MySQL server from group: mysqld3306 is running MySQL server from group: mysqld3307 is running # 启动单个实例 [root@localhost data]# /usr/local/mysql/bin/mysqld_multi start 3306 # 查看单个实例状态 [root@localhost data]# /usr/local/mysql/bin/mysqld_multi report 3306 Reporting MySQL servers MySQL server from group: mysqld3306 is running # 停止单个实例 [root@localhost data]# /usr/local/mysql/bin/mysqld_multi stop 3306
mysql登陆及密码修改
启动全部实例
[root@localhost tmp]# /usr/local/mysql/bin/mysqld_multi start [root@localhost tmp]# /usr/local/mysql/bin/mysqld_multi report Reporting MySQL servers MySQL server from group: mysqld3306 is running MySQL server from group: mysqld3307 is running
登陆修改密码
[root@localhost ~]# mysql -S /tmp/mysql_3306.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.22-log MySQL Community Server (GPL) Copyright (c) 2000, 2014, 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 for root@'localhost'=password('123456'); Query OK, 0 rows affected (0.00 sec) mysql>
退出重新登陆，后面加 -p 输入密码
[root@localhost ~]# mysql -S /tmp/mysql_3306.sock -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.6.22-log MySQL Community Server (GPL) Copyright (c) 2000, 2014, 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>
配置运程连接
[root@localhost ~]# mysql -S /tmp/mysql_3306.sock -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.6.22-log MySQL Community Server (GPL) Copyright (c) 2000, 2014, 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> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION; Query OK, 0 rows affected (0.05 sec) mysql> flush privileges; Query OK, 0 rows affected (0.05 sec) mysql>
使用Navicat连接mysql数据库
创建一个database
mysql> create database db_3306; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db_3306 | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) mysql>
刷新Navicat可以看到已经创建成功
新增一个实例
增加一个3308实例
创建相关目录并授权
[root@localhost ~]# mkdir /data/mysql/mysql_3308 [root@localhost ~]# mkdir /data/mysql/mysql_3308/{data,log,tmp} [root@localhost ~]# chown -R mysql:mysql /data/mysql/mysql_3308/
在my.cnf添加配置
[root@localhost ~]# vi /etc/my.cnf
添加3308配置
[mysqld3308] mysqld=mysqld mysqladmin=mysqladmin datadir=/data/mysql/mysql_3308/data port=3308 server_id=3308 socket=/tmp/mysql_3308.sock log-output=file slow_query_log = 1 long_query_time = 1 slow_query_log_file = /data/mysql/mysql_3308/log/slow.log log-error = /data/mysql/mysql_3308/log/error.log binlog_format = mixed log-bin = /data/mysql/mysql_3308/log/mysql3308_bin
初始化3308数据库
/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/data/mysql/mysql_3308/data --defaults-file=/etc/my.cnf
启动3308并进行相关设置
# 启动3308数据库 [root@localhost ~]# /usr/local/mysql/bin/mysqld_multi start 3308 # 登陆3308数据库 [root@localhost ~]# mysql -S /tmp/mysql_3308.sock # 配置运程连接 mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec) # 刷新权限 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) # 设置密码 mysql> set password for root@'localhost'=password('123456'); Query OK, 0 rows affected (0.00 sec)
Navicat连接测试
查看启动的所有实例信息
[root@localhost ~]# ps -ef|grep mysql mysql 2635 1 0 11:39 pts/0 00:00:08 mysqld --datadir=/data/mysql/mysql_3306/data --port=3306 --server_id=3306 --socket=/tmp/mysql_3306.sock --log-output=file --slow_query_log=1 --long_query_time=1 --slow_query_log_file=/data/mysql/mysql_3306/log/slow.log --log-error=/data/mysql/mysql_3306/log/error.log --binlog_format=mixed --log-bin=/data/mysql/mysql_3306/log/mysql3306_bin mysql 2702 1 0 11:48 pts/0 00:00:09 mysqld --datadir=/data/mysql/mysql_3307/data --port=3307 --server_id=3307 --socket=/tmp/mysql_3307.sock --log-output=file --slow_query_log=1 --long_query_time=1 --slow_query_log_file=/data/mysql/mysql_3307/log/slow.log --log-error=/data/mysql/mysql_3307/log/error.log --binlog_format=mixed --log-bin=/data/mysql/mysql_3307/log/mysql3307_bin --sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES mysql 2925 1 0 11:56 pts/0 00:00:03 mysqld --datadir=/data/mysql/mysql_3308/data --port=3308 --server_id=3308 --socket=/tmp/mysql_3308.sock --log-output=file --slow_query_log=1 --long_query_time=1 --slow_query_log_file=/data/mysql/mysql_3308/log/slow.log --log-error=/data/mysql/mysql_3308/log/error.log --binlog_format=mixed --log-bin=/data/mysql/mysql_3308/log/mysql3308_bin --sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES root 27083 2124 1 12:29 pts/0 00:00:00 grep mysql [root@localhost ~]#
