前言
MySQL的使用
MySQL多表同时删除方案
MySQL跨表、多表更新SQL语句总结
MySQL存储引擎
安装
常规方式编译安装MySQL
采用cmake方式编译安装MySQL
使用rpm包安装MySQL
使用yum方式安装MySQL
采用二进制方式免编译安装MySQL
多实例的安装
什么是多实例
多实例的作用、问题以及应用场景
多实例安装01【推荐】
多实例官方安装方案02
启动、用户和权限管理
单实例MySQL的启动和关闭的方法
设置及修改MySQL root用户密码
找回丢失的MySQL root用户密码
创建MySQL用户及用户权限管理
基础命令的操作
MySQL库和表相关操作
MySQL中的索引操作
MySQL常用命令
MySQL的错误代码
MySQL复习秘籍
备份与恢复
备份
恢复
mysqlbinlog命令
服务日志
主从复制
主从复制部署配置问题汇总
主从复制读写分离
灾难恢复
配置phpmyadmin连接多实例MySQL
MySQL语句大全
用户创建、权限、删除
数据库与表显示、创建、删除
Mysql表复制及备份还原
数据库表中数据操作
修改表中的指定一条数据
查询表
日志
批量修改Mysql表引擎为InnoDB的方法
数据库抽象层 PDO
PDO对象常用方法
PDO 事务处理
PDO 与 MySQLi 二者效率简单比较
大小写敏感性 lower_case_table_names
CentOS7安装MySQL5.7密码查看与修改
©
本文档使用 PHP中文网手册 发布
文字
再来看第二种通过官方自带的mysqld_multi实现多实例实战：
通过官方自带的mysqld_multi使用单独的配置文件来实现多实例，这种方式定制每个实例的配置不太方面，优点是管理起来很方便，集中管理。下面就分别来实战这两种多实例的安装和管理
环境介绍：
mysql 版本：5.5.29
操作系统：Centos 5.5
mysql实例数：3个
实例占用端口分别为：3306、3307、3308
这里的mysql安装以及数据库的初始化和前面的步骤一样。
必要软件包
yum -y install ncurses-devel gcc gcc-c++ gcc-g77 autoconf automake zlib* fiex* libxml* libmcrypt* libtool-ltdl-devel* libtool make cmake
创建mysql用户
/usr/sbin/groupadd mysql /usr/sbin/useradd -g mysql mysql -s /sbin/nologin
编译安装MySQL
tar -zxvf mysql-5.5.29.tar.gz && cd mysql-5.5.29 #默认情况下是安装在/usr/local/mysql cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ -DMYSQL_DATADIR=/usr/local/mysql/data \ -DSYSCONFDIR=/etc \ -DWITH_MYISAM_STORAGE_ENGINE=1 \ -DWITH_INNOBASE_STORAGE_ENGINE=1 \ -DWITH_MEMORY_STORAGE_ENGINE=1 \ -DWITH_READLINE=1 \ -DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.sock \ -DMYSQL_TCP_PORT=3306 \ -DENABLED_LOCAL_INFILE=1 \ -DWITH_PARTITION_STORAGE_ENGINE=1 \ -DEXTRA_CHARSETS=all \ -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci make && make install
初始化数据库
/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/dbdata_3306/data --user=mysql /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/dbdata_3307/data --user=mysql /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/dbdata_3308/data --user=mysql
为什么要初始化数据库?
答:初始化的主要目的就是创建基础的数据库文件,例如生成MySQL库,表等.
授权数据库权限给mysql用户
setfacl -m u:mysql:rwx -R /data setfacl -m d:u:mysql:rwx -R /data
mysqld_multi的配置
vim /etc/my.cnf
[mysqld_multi] mysqld = /usr/local/mysql/bin/mysqld_safe mysqladmin = /usr/local/mysql/bin/mysqladmin user = admin password = password [mysqld1] socket = /data/dbdata_3306/mysql.sock port = 3306 pid-file = /data/dbdata_3306/3306.pid datadir = /data/dbdata_3306/data user = mysql skip-name-resolve lower_case_table_names=1 innodb_file_per_table=1 back_log = 50 max_connections = 300 max_connect_errors = 1000 table_open_cache = 2048 max_allowed_packet = 16M binlog_cache_size = 2M max_heap_table_size = 64M sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size = 64 thread_concurrency = 8 query_cache_size = 64M query_cache_limit = 2M ft_min_word_len = 4 default-storage-engine = innodb thread_stack = 192K transaction_isolation = REPEATABLE-READ tmp_table_size = 64M log-bin=mysql-bin binlog_format=mixed slow_query_log long_query_time = 1 server-id = 3306 key_buffer_size = 8M read_buffer_size = 2M read_rnd_buffer_size = 2M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 200M innodb_data_file_path = ibdata1:10M:autoextend innodb_file_io_threads = 8 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 16M innodb_log_file_size = 512M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 60 innodb_lock_wait_timeout = 120 [mysqld2] socket = /data/dbdata_3307/mysql.sock port = 3307 pid-file = /data/dbdata_3307/3307.pid datadir = /data/dbdata_3307/data user = mysql skip-name-resolve lower_case_table_names=1 innodb_file_per_table=1 back_log = 50 max_connections = 300 max_connect_errors = 1000 table_open_cache = 2048 max_allowed_packet = 16M binlog_cache_size = 2M max_heap_table_size = 64M sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size = 64 thread_concurrency = 8 query_cache_size = 64M query_cache_limit = 2M ft_min_word_len = 4 default-storage-engine = innodb thread_stack = 192K transaction_isolation = REPEATABLE-READ tmp_table_size = 64M log-bin=mysql-bin binlog_format=mixed slow_query_log long_query_time = 1 server-id = 3307 key_buffer_size = 8M read_buffer_size = 2M read_rnd_buffer_size = 2M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 200M innodb_data_file_path = ibdata1:10M:autoextend innodb_file_io_threads = 8 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 16M innodb_log_file_size = 512M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 60 innodb_lock_wait_timeout = 120 [mysqld3] socket = /data/dbdata_3308/mysql.sock port = 3308 pid-file = /data/dbdata_3308/3308.pid datadir = /data/dbdata_3308/data user = mysql skip-name-resolve lower_case_table_names=1 innodb_file_per_table=1 back_log = 50 max_connections = 300 max_connect_errors = 1000 table_open_cache = 2048 max_allowed_packet = 16M binlog_cache_size = 2M max_heap_table_size = 64M sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size = 64 thread_concurrency = 8 query_cache_size = 64M query_cache_limit = 2M ft_min_word_len = 4 default-storage-engine = innodb thread_stack = 192K transaction_isolation = REPEATABLE-READ tmp_table_size = 64M log-bin=mysql-bin binlog_format=mixed slow_query_log long_query_time = 1 server-id = 3308 key_buffer_size = 8M read_buffer_size = 2M read_rnd_buffer_size = 2M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 200M innodb_data_file_path = ibdata1:10M:autoextend innodb_file_io_threads = 8 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 16M innodb_log_file_size = 512M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 60 innodb_lock_wait_timeout = 120 [mysqldump] quick max_allowed_packet = 256M [mysql] no-auto-rehash prompt=\\u@rose \\r:\\m:\\s> [myisamchk] key_buffer_size = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout [mysqld_safe] open-files-limit = 8192
更改环境变量
export PATH=/usr/local/mysql/bin:$PATH
# 或者
vim /etc/profile
PATH=/usr/local/mysql/bin:${PATH}
source /etc/profile
mysqld_multi启动
/usr/local/mysql/bin/mysqld_multi start 1 /usr/local/mysql/bin/mysqld_multi start 2 /usr/local/mysql/bin/mysqld_multi start 3 # 或采用一条命令的形式 /usr/local/mysql/bin/mysqld_multi start 1-3
更改原来密码（出于安全考虑，还需要删除系统中没有密码的帐号）
/usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata_3306/mysql.sock /usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata_3307/mysql.sock /usr/local/mysql/bin/mysqladmin -uroot password 'password' -S /data/dbdata_3308/mysql.sock
登录MySQL并创建admin账户密码(停止mysql的时候需要使用)
/usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3306/mysql.sock GRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password'; flush privileges; /usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3307/mysql.sock GRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password'; flush privileges; /usr/local/mysql/bin/mysql -uroot -ppassword -S /data/dbdata_3308/mysql.sock GRANT SHUTDOWN ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password'; flush privileges;
mysqld_multi关闭进程(测试无效)
/usr/local/mysql/bin/mysqld_multi stop 1 /usr/local/mysql/bin/mysqld_multi stop 1-3
管理的话，在本地都是采用 -S /data/dbdata_3308/mysql.sock，如果在远程可以通过不同的端口连接上去做管理操作。其他的和单实例的管理没什么区别！
相关博文参考：
mysql多实例的配置和管理