mysql多实例安装(mysql基本命令大全)-众学网

01 MySQL 5.7 多实例单配置文件安装

1.创建mysql用户以及组

groupadd mysql useradd -r -g mysql -s /bin/false mysql

2.创建Mysql数据目录

mkdir -p /data/mysql/3306mkdir -p /data/mysql/3307mkdir -p /data/mysql/3308chown -R mysql.mysql /data/

3.创建mysql logs目录

mkdir -p /logs/mysql/3306mkdir -p /logs/mysql/3307mkdir -p /logs/mysql/3308mkdir -p /var/run/mysqldchown -R mysql.mysql /logs/chown -R mysql.mysql /var/run/mysqld

4.解压Mysql安装包

tar xvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz -C /cd /mv mysql-5.7.26-linux-glibc2.12-x86_64/ mysqlchown -R mysql.mysql /mysql

5.将Mysql加入环境变量

[root@mysqlmulti /]# . ~/.bash_profile [root@mysqlmulti /]# cat ~/.bash_profile # .bash_profile# Get the aliases and functionsif [ -f ~/.bashrc ]; then . ~/.bashrcfi# User specific environment and startup programsPATH=$PATH:$HOME/bin:/mysql/binexport PATH

6.初始化数据

cd /mysql/bin./mysqld –initialize –user=mysql –basedir=/mysql –datadir=/data/mysql/3306 –explicit_defaults_for_timestamp./mysqld –initialize –user=mysql –basedir=/mysql –datadir=/data/mysql/3307 –explicit_defaults_for_timestamp./mysqld –initialize –user=mysql –basedir=/mysql –datadir=/data/mysql/3308 –explicit_defaults_for_timestamp

7.修改配置文件

[root@mysqlmulti run]# cat /etc/my.cnf[mysqld_multi] mysqld = /mysql/bin/mysqld_safe mysqladmin = /mysql/bin/mysqladmin [mysqld3306] port = 3306 server_id = 3306 basedir =/mysql datadir =/data/mysql/3306 log-bin=/data/mysql/3306/mysql-bin socket =/tmp/mysql3306.sock log-error =/logs/mysql/3306/mysqld3306.log pid-file =/var/run/mysqld/mysqld3306.pid [mysqld3307] port = 3307 server_id = 3307 basedir =/mysql datadir =/data/mysql/3307 log-bin=/data/mysql/3307/mysql-bin socket =/tmp/mysql3307.sock log-error =/logs/mysql/3307/mysqld3307.log pid-file =/var/run/mysqld/mysqld3307.pid [mysqld3308]port = 3308server_id = 3308basedir =/mysql datadir =/data/mysql/3308log-bin=/data/mysql/3308/mysql-bin socket =/tmp/mysql3308.socklog-error =/logs/mysql/3308/mysqld3308.logpid-file =/var/run/mysqld/mysqld3308.pid

8.创建日志文件

touch /logs/mysql/3306/mysqld3306.log touch /logs/mysql/3307/mysqld3307.log touch /logs/mysql/3308/mysqld3308.log chown -R mysql.mysql /logs/

9.启动3306,3307,3308实例

mysqld_multi start 3306mysqld_multi start 3307mysqld_multi start 3308

10.访问3306、3307、3308实例，密码是第六步初始化数据生成的密码

mysql -uroot -p -S /tmp/mysql3306.sockmysql -uroot -p -S /tmp/mysql3307.sockmysql -uroot -p -S /tmp/mysql3308.sock

11.更改密码

mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘wwwwww’; mysql> FLUSH PRIVILEGES;

12.查看多实例状态

[root@mysqlmulti bin]# mysqld_multi reportWARNING: Log file disabled. Maybe directory or file isn’t writable?mysqld_multi log file version 2.16; run: Sat Jul 13 23:38:27 2019Reporting MySQL serversMySQL server from group: mysqld3306 is runningMySQL server from group: mysqld3307 is runningMySQL server from group: mysqld3308 is running

02 MySQL 5.7多实例多配置文件安装

本文旨在学习多实例多个cnf配置文件下的安装与配置。

本文中创建了3306、3307、3308三个实例。

1.创建mysql用户以及组

groupadd mysqluseradd -r -g mysql -s /bin/false mysql

2.创建mysql数据目录

mkdir -p /data/{3306,3307,3308}/datachown -R mysql.mysql /data/

3.创建mysql logs目录

mkdir -p /logs/{3306,3307,3308}mkdir -p /logs/{3306,3307,3308}/redologmkdir -p /logs/{3306,3307,3308}/undologtouch /logs/3306/mysqld3306.log touch /logs/3307/mysqld3307.log touch /logs/3308/mysqld3308.log chown -R mysql.mysql /logs/mkdir -p /var/run/mysqldchown -R mysql.mysql /var/run/mysqld

4.解压Mysql安装包

tar xvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz -C /cd /mv mysql-5.7.26-linux-glibc2.12-x86_64/ mysqlchown -R mysql.mysql /mysql

5.将Mysql加入环境变量

6.修改配置文件

修改/data/3306/my3306.cnf文件

[root@mysql57 3306]# cat my3306.cnf [client]port = 3306socket = /data/3306/mysql3306.sock[mysql]auto-rehash[mysqld_safe]log-error = /logs/3306/mysqld3306.logpid-error = /data/3306/mysql3306.pid[mysqld]user = mysqlport = 3306socket= /data/3306/mysql3306.sockpid-file = /data/3306/mysql3306.piddatadir = /data/3306/databasedir = /mysqlskip_name_resolve = 1skip-external-lockingmax_connections = 3000max_connect_errors = 10transaction_isolation = READ-COMMITTEDinteractive_timeout=86400wait_timeout=86400back_log=600####cache###### table_open_cache=2000thread_cache_size=500query_cache_size=128Mquery_cache_min_res_unit=128ksort_buffer_size=2Mread_buffer_size=2Mread_rnd_buffer_size=4Mjoin_buffer_size=2Mbulk_insert_buffer_size=16Mmax_heap_table_size=64Mtmp_table_size=64M###MyISAM####key_buffer_size=64Mkey_cache_block_size=4kmyisam_sort_buffer_size=2M########innodb settings#########innodb_page_size = 4kinnodb_buffer_pool_size = 1Ginnodb_buffer_pool_instances = 1innodb_buffer_pool_load_at_startup = 1innodb_buffer_pool_dump_at_shutdown = 1#innodb_lru_scan_depth = 2000innodb_lock_wait_timeout = 50#innodb_io_capacity = 4000#innodb_io_capacity_max = 8000innodb_flush_method = O_DIRECTinnodb_log_group_home_dir = /logs/3306/redolog/innodb_undo_directory = /logs/3306/undolog/innodb_undo_logs = 128#innodb_undo_tablespaces = 3innodb_flush_neighbors = 1innodb_log_file_size = 256Minnodb_log_buffer_size = 8Minnodb_purge_threads = 4innodb_large_prefix = 1innodb_thread_concurrency = 64innodb_print_all_deadlocks = 1innodb_strict_mode = 1innodb_sort_buffer_size = 4M######mysqld-5.7########innodb_buffer_pool_dump_pct = 40innodb_page_cleaners = 4innodb_undo_log_truncate = 1innodb_max_undo_log_size = 2Ginnodb_purge_rseg_truncate_frequency = 128binlog_gtid_simple_recovery=1log_timestamps=systemtransaction_write_set_extraction=MURMUR32show_compatibility_56=onsql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES#skip-grant-tables

修改/data/3307/my3307.cnf文件

[root@mysql57 3307]# cat my3307.cnf [client]port = 3307socket = /data/3307/mysql3307.sock[mysql]auto-rehash[mysqld_safe]log-error = /logs/3307/mysqld3307.logpid-error = /data/3307/mysql3307.pid[mysqld]user = mysqlport = 3307socket= /data/3307/mysql3307.sockpid-file = /data/3307/mysql3307.piddatadir = /data/3307/databasedir = /mysqlskip_name_resolve = 1skip-external-lockingmax_connections = 3000max_connect_errors = 10transaction_isolation = READ-COMMITTEDinteractive_timeout=86400wait_timeout=86400back_log=600####cache###### table_open_cache=2000thread_cache_size=500query_cache_size=128Mquery_cache_min_res_unit=128ksort_buffer_size=2Mread_buffer_size=2Mread_rnd_buffer_size=4Mjoin_buffer_size=2Mbulk_insert_buffer_size=16Mmax_heap_table_size=64Mtmp_table_size=64M###MyISAM####key_buffer_size=64Mkey_cache_block_size=4kmyisam_sort_buffer_size=2M########innodb settings#########innodb_page_size = 4kinnodb_buffer_pool_size = 1Ginnodb_buffer_pool_instances = 1innodb_buffer_pool_load_at_startup = 1innodb_buffer_pool_dump_at_shutdown = 1#innodb_lru_scan_depth = 2000innodb_lock_wait_timeout = 50#innodb_io_capacity = 4000#innodb_io_capacity_max = 8000innodb_flush_method = O_DIRECTinnodb_log_group_home_dir = /logs/3307/redolog/innodb_undo_directory = /logs/3307/undolog/innodb_undo_logs = 128#innodb_undo_tablespaces = 3innodb_flush_neighbors = 1innodb_log_file_size = 256Minnodb_log_buffer_size = 8Minnodb_purge_threads = 4innodb_large_prefix = 1innodb_thread_concurrency = 64innodb_print_all_deadlocks = 1innodb_strict_mode = 1innodb_sort_buffer_size = 4M######mysqld-5.7########innodb_buffer_pool_dump_pct = 40innodb_page_cleaners = 4innodb_undo_log_truncate = 1innodb_max_undo_log_size = 2Ginnodb_purge_rseg_truncate_frequency = 128binlog_gtid_simple_recovery=1log_timestamps=systemtransaction_write_set_extraction=MURMUR32show_compatibility_56=onsql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES#skip-grant-tables

修改/data/3308/my3308.cnf文件

[root@mysql57 3308]# cat my3308.cnf [client]port = 3308socket = /data/3308/mysql3308.sock[mysql]auto-rehash[mysqld_safe]log-error = /logs/3308/mysqld3308.logpid-error = /data/3308/mysql3308.pid[mysqld]user = mysqlport = 3308socket= /data/3308/mysql3308.sockpid-file = /data/3308/mysql3308.piddatadir = /data/3308/databasedir = /mysqlskip_name_resolve = 1skip-external-lockingmax_connections = 3000max_connect_errors = 10transaction_isolation = READ-COMMITTEDinteractive_timeout=86400wait_timeout=86400back_log=600####cache###### table_open_cache=2000thread_cache_size=500query_cache_size=128Mquery_cache_min_res_unit=128ksort_buffer_size=2Mread_buffer_size=2Mread_rnd_buffer_size=4Mjoin_buffer_size=2Mbulk_insert_buffer_size=16Mmax_heap_table_size=64Mtmp_table_size=64M###MyISAM####key_buffer_size=64Mkey_cache_block_size=4kmyisam_sort_buffer_size=2M########innodb settings#########innodb_page_size = 4kinnodb_buffer_pool_size = 1Ginnodb_buffer_pool_instances = 1innodb_buffer_pool_load_at_startup = 1innodb_buffer_pool_dump_at_shutdown = 1#innodb_lru_scan_depth = 2000innodb_lock_wait_timeout = 50#innodb_io_capacity = 4000#innodb_io_capacity_max = 8000innodb_flush_method = O_DIRECTinnodb_log_group_home_dir = /logs/3308/redolog/innodb_undo_directory = /logs/3308/undolog/innodb_undo_logs = 128#innodb_undo_tablespaces = 3innodb_flush_neighbors = 1innodb_log_file_size = 256Minnodb_log_buffer_size = 8Minnodb_purge_threads = 4innodb_large_prefix = 1innodb_thread_concurrency = 64innodb_print_all_deadlocks = 1innodb_strict_mode = 1innodb_sort_buffer_size = 4M######mysqld-5.7########innodb_buffer_pool_dump_pct = 40innodb_page_cleaners = 4innodb_undo_log_truncate = 1innodb_max_undo_log_size = 2Ginnodb_purge_rseg_truncate_frequency = 128binlog_gtid_simple_recovery=1log_timestamps=systemtransaction_write_set_extraction=MURMUR32show_compatibility_56=onsql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES#skip-grant-tables

6.初始化数据

cd /mysql/bin[root@mysql57 bin]# ./mysqld –initialize –user=mysql –basedir=/mysql –datadir=/data/3306/data –explicit_defaults_for_timestamp2019-07-15T11:51:33.672753Z 0 [Warning] InnoDB: New log files created, LSN=457902019-07-15T11:51:33.809864Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.2019-07-15T11:51:33.883915Z 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: e497ef6e-a6f6-11e9-8d94-080027704b98.2019-07-15T11:51:33.923591Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed’ cannot be opened.2019-07-15T11:51:33.924124Z 1 [Note] A temporary password is generated for root@localhost: dxg4gx7od_0Y[root@mysql57 bin]# ./mysqld –initialize –user=mysql –basedir=/mysql –datadir=/data/3307/data –explicit_defaults_for_timestamp2019-07-15T12:04:40.909570Z 0 [Warning] InnoDB: New log files created, LSN=457902019-07-15T12:04:41.109548Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.2019-07-15T12:04:41.189840Z 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: b9dd499e-a6f8-11e9-aafd-080027704b98.2019-07-15T12:04:41.191921Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed’ cannot be opened.2019-07-15T12:04:41.192590Z 1 [Note] A temporary password is generated for root@localhost: yhFw?#fPl2&N[root@mysql57 bin]# ./mysqld –initialize –user=mysql –basedir=/mysql –datadir=/data/3308/data –explicit_defaults_for_timestamp2019-07-15T12:14:45.071994Z 0 [Warning] InnoDB: New log files created, LSN=457902019-07-15T12:14:45.286324Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.2019-07-15T12:14:45.373528Z 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: 21fc66ed-a6fa-11e9-a44b-080027704b98.2019-07-15T12:14:45.375971Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed’ cannot be opened.2019-07-15T12:14:45.376668Z 1 [Note] A temporary password is generated for root@localhost: u9;Cqar8>gaP

7.启动mysql，在/data/3306下创建mysql脚本，内容如下：

[root@mysql57 3306]# cat mysql#!/bin/shport=3306mysql_user=”root”mysql_pwd=”wwwwww”CmdPath=”/mysql/bin”mysql_sock=”/data/${port}/mysql3306.sock”#start Mysql Servicesfunction_start_mysql(){ if [ ! -e “$mysql_sock” ];then printf “Starting MySQL…\n” /bin/sh ${CmdPath}/mysqld_safe –defaults-file=/data/${port}/my3306.cnf 2>&1 > /dev/null & else printf “MySQL is running…\n” exit fi}#stop Mysql Servicesfunction_stop_mysql(){ if [ ! -e “$mysql_sock” ];then printf “MySQL is stopped…\n” exit else printf “Stoping MySQL…\n” ${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql3306.sock shutdown fi}#restart Mysql Servicesfunction_restart_mysql(){ printf “Restarting MySQL…\n” function_stop_mysql sleep 2 function_start_mysql}case $1 instart) function_start_mysql;;stop) function_stop_mysql;;restart) function_restart_mysql;;*) printf “Usage: /data/${port}/mysql {start|stop|restart}\n”esac

分别在/data/3307和/data/3308下也创建类似mysql，需要需要里面3306为3307或3308

执行命令启动3306、3307、3308实例

[root@mysql57 bin]# /data/3306/mysql startMySQL is running…[root@mysql57 bin]# /data/3307/mysql startMySQL is running…[root@mysql57 bin]# /data/3308/mysql startMySQL is running…

8.访问3306、3307、3308实例，密码是第六步初始化数据生成的密码

mysql -uroot -p -S /data/3306/mysql3306.sockmysql -uroot -p -S /data/3307/mysql3307.sockmysql -uroot -p -S /data/3308/mysql3308.sock

9.更改密码

mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘wwwwww’; mysql> FLUSH PRIVILEGES;

10.查看多实例状态

[root@mysql57 ~]# ps -ef | grep mysqld | grep -v greproot 6832 1 0 20:00 pts/1 00:00:00 /bin/sh /mysql/bin/mysqld_safe –defaults-file=/data/3306/my3306.cnfmysql 7594 6832 0 20:00 pts/1 00:00:03 /mysql/bin/mysqld –defaults-file=/data/3306/my3306.cnf –basedir=/mysql –datadir=/data/3306/data –plugin-dir=/mysql/lib/plugin –user=mysql –log-error=/logs/3306/mysqld3306.log –pid-file=/data/3306/mysql3306.pid –socket=/data/3306/mysql3306.sock –port=3306root 7810 1 0 20:13 pts/2 00:00:00 /bin/sh /mysql/bin/mysqld_safe –defaults-file=/data/3307/my3307.cnfmysql 8572 7810 1 20:13 pts/2 00:00:06 /mysql/bin/mysqld –defaults-file=/data/3307/my3307.cnf –basedir=/mysql –datadir=/data/3307/data –plugin-dir=/mysql/lib/plugin –user=mysql –log-error=/logs/3307/mysqld3307.log –pid-file=/data/3307/mysql3307.pid –socket=/data/3307/mysql3307.sock –port=3307root 8892 1 0 20:20 pts/5 00:00:00 /bin/sh /mysql/bin/mysqld_safe –defaults-file=/data/3308/my3308.cnfmysql 9654 8892 5 20:20 pts/5 00:00:05 /mysql/bin/mysqld –defaults-file=/data/3308/my3308.cnf –basedir=/mysql –datadir=/data/3308/data –plugin-dir=/mysql/lib/plugin –user=mysql –log-error=/logs/3308/mysqld3308.log –pid-file=/data/3308/mysql3308.pid –socket=/data/3308/mysql3308.sock –port=3308[root@mysql57 3308]# netstat -unltp | grep 3306 tcp6 0 0 :::3306 :::* LISTEN 7594/mysqld [root@mysql57 3308]# netstat -unltp | grep 3307tcp6 0 0 :::3307 :::* LISTEN 8572/mysqld [root@mysql57 3308]# netstat -unltp | grep 3308tcp6 0 0 :::3308 :::* LISTEN 9654/mysqld

11.停止mysql

[root@mysql57 3308]# /data/3306/mysql stop && /data/3307/mysql stop && /data/3308/mysql stopStoping MySQL…mysqladmin: [Warning] Using a password on the command line interface can be insecure.Stoping MySQL…mysqladmin: [Warning] Using a password on the command line interface can be insecure.Stoping MySQL…mysqladmin: [Warning] Using a password on the command line interface can be insecure.

mysql 5.7多实例单配置文件安装

http://www.talkwithtrend.com/Article/245297

mysql 5.7多实例多配置文件安装

http://www.talkwithtrend.com/Article/245307

资料/文章推荐：

MySQL 实用知识和技巧 29 篇文章 10 篇文档

Mysql最佳安全配置手册

http://www.talkwithtrend.com/Document/detail/tid/426401

Mysql5.7—运维常用备份方式（超全）

http://www.talkwithtrend.com/Document/detail/tid/402419

http://www.talkwithtrend.com/Topic/3873

下载 twt 社区客户端 APP