mysql 多实例案例实战
来源：互联网 发布：买p2p问题平台数据 编辑：程序博客网 时间：2024/06/05 15:27
其实Mysql多实例就是在一个 mysql 服务上面启动三个实例，相当于三个分离开来的数据库，至于为什么要做这个，你也可以选择分别安装三个MySQL，只是过于麻烦，多实例中只需要一个配置档my.cnf，并且通过mysql_330x.sock 便于管理数据库。
其实MySQL多实例之后也可以做主从同步及读写分离，跟原来是一样的，只是同步的时候端口有变化而已~
下面我来做MySQL多实例的环境搭建，至于如何用，怎么用，那就看你需求了
安装简介
- 用户名：mysql
- 安装目录：/usr/local/mysql
- 实例1数据目录 ： /usr/local/mysql/data/dbdata_3306
- 实例2数据目录 ： /usr/local/mysql/data/dbdata_3307
- 实例3数据目录 ： /usr/local/mysql/data/dbdata_3308
安装前步骤
一、添加MySQL用户
groupadd mysql
useradd -g mysql -s /sbin/nologin mysql
二、yum安装依赖包
autoconf automake bc bind-utils bzip2 bzip2-devel curl curl-devel dmidecode e2fsprogs-devel expat expat-devel freetype* gcc gcc-c++ gettext gettext-devel glib2 glib2-devel glibc glibc-devel hdparm krb5-devel libaio libaio-devel libidn-devel libjpeg libpng libxml2 libxml2-devel lrzsz mlocate ncurses ncurses-devel net-snmp net-snmp-devel net-snmp-libs net-snmp-utils ntp openssh-clients openssl-devel pam-devel rsync rsyslog-relp screen smartmontools strace sysstat telnet vim* vixie-cron wget yum-fastestmirror zip zlib-devel
三、解包
tar zxf mysql-5.6.14.tar.gz -C /usr/src
cd /usr/src/mysql-5.6.14
四、创建数据目录
mkdir -p /usr/local/mysql/data
五、cmake配置
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_MYISAM_STORAGE_ENGING=1 -DWITH_INNOBASE_STORAGE_ENGING=1 -DWITH_SSL=yes
六、编译及安装
make && make install
七、拷贝配置文件
cp support-files/my-default.cnf /usr/local/mysql/data/my.cnf
八、初始化数据库
/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/dbdata_3306 --user=mysql/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/dbdata_3307 --user=mysql/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/dbdata_3308 --user=mysql九、设置链接
ln -s /usr/local/mysql/bin/* /usr/local/bin/
ln -s /usr/local/mysql/include/mysql/* /usr/include/
ln -s /usr/local/mysql/lib/* /usr/lib/
rm -rf /etc/my.cnf
ln -s /usr/local/mysql/data/my.cnf /etc/
十、设置权限
chown -R mysql:mysql /usr/local/mysql/data/dbdata_3306
chown -R mysql:mysql /usr/local/mysql/data/dbdata_3307
chown -R mysql:mysql /usr/local/mysql/data/dbdata_3308
十一、修改my.cnf配置
#!/bin/bash[mysqld_multi]mysqld = /usr/local/mysql/bin/mysqld_safemysqladmin = /usr/local/mysql/bin/mysqladminuser = rootpassword = yunjee0515ueopro1234[mysqld1]basedir = /usr/local/mysqlcharacter-set-server = utf8port = 3306socket = /tmp/mysql_3306.sockdatadir = /usr/local/mysql/data/dbdata_3306pid-file = /usr/local/mysql/data/dbdata_3306/mysql.pidlog-error = /usr/local/mysql/data/dbdata_3306/mysql.errserver-id = 1skip-character-set-client-handshake #忽略应用程序想要设置的其他字符集init-connect='SET NAMES utf8' #连接时执行的SQLcharacter-set-server=utf8 #服务端默认字符集wait_timeout=1800 #请求的最大连接时间interactive_timeout=1800 #和上一参数同时修改才会生效sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES #sql模式log-bin = mysql-bin #打开二进制功能,MASTER主服务器必须打开此项relay-log = mysql-bin #文件名格式relay-log-index = mysql-bin.index #index文件名##MySQL 5.6.10版本提供了更方便的基于GTID的复制功能，MySQL可以通过GTID自动识别上次同步的点，极大地方便了运维人员，减少出错的几率。binlog-format=ROW #binlog格式log-slave-updates=true #表示如果一个MASTER挂掉的话，另外一个马上接管gtid-mode=on #用于启动GTID及满足附属的其它需求enforce-gtid-consistency=true ##report-port=port #从属服务器的端口#report-host=host #从属服务器的主机名master-info-repository=TABLE #启用此两项，可用于实现在崩溃时保证二进制及从服务器安全的功能relay-log-info-repository=TABLE #sync-master-info=1 #启用之可确保无信息丢失slave-parallel-workers=2 #设定从服务器的SQL线程数；0表示关闭多线程复制功能binlog-checksum=CRC32 #master-verify-checksum=1 #slave-sql-verify-checksum=1 #启用复制有关的所有校验功能binlog-rows-query-log_events=1 ###MySQL 5.6.10版本提供了更方便的基于GTID的复制功能，MySQL可以通过GTID自动识别上次同步的点，极大地方便了运维人员，减少出错的几率。expire_logs_day=5 #超过5天的binlog删除 max_binlog_size=104857600#replicate-ignore-db = mysql #忽略不同步主从的数据库#replicate-ignore-db = information_schema#replicate-ignore-db = performance_schema#replicate-ignore-db = test#replicate-ignore-db = zabbix#replicate-ignore-db = sysbench#replicate-ignore-db = db_3306##slave-skip-errors=0 #主从忽略错误数##注释掉,使用默认设置#skip-external-locking##innodb_force_recovery = 1key_buffer_size = 256Mmax_allowed_packet = 100M#table_open_cache = 1024#sort_buffer_size = 128M#net_buffer_length = 8K#read_buffer_size = 128M#read_rnd_buffer_size = 256M#myisam_sort_buffer_size = 32M#character-set-server = utf8skip-name-resolvemax_connections = 10000##慢查询设置slow-query-log=onlong_query_time = 2#log-queries-not-using-indexes##注释掉,使用默认设置## For InnoDBinnodb_buffer_pool_size = 1G#innodb_additional_mem_pool_size = 128M## Set .._log_file_size to 25 % of buffer pool size#innodb_log_file_size = 128M#innodb_log_buffer_size = 8Minnodb_flush_log_at_trx_commit = 1#innodb_lock_wait_timeout = 50#innodb_file_per_table=1[mysqld2]basedir = /usr/local/mysqlcharacter-set-server = utf8port = 3307socket = /tmp/mysql_3307.sockdatadir = /usr/local/mysql/data/dbdata_3307pid-file = /usr/local/mysql/data/dbdata_3307/mysql.pidlog-error = /usr/local/mysql/data/dbdata_3307/mysql.errserver-id = 1skip-character-set-client-handshake #忽略应用程序想要设置的其他字符集init-connect='SET NAMES utf8' #连接时执行的SQLcharacter-set-server=utf8 #服务端默认字符集wait_timeout=1800 #请求的最大连接时间interactive_timeout=1800 #和上一参数同时修改才会生效sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES #sql模式log-bin = mysql-bin #打开二进制功能,MASTER主服务器必须打开此项relay-log = mysql-bin #文件名格式relay-log-index = mysql-bin.index #index文件名#MySQL 5.6.10版本提供了更方便的基于GTID的复制功能，MySQL可以通过GTID自动识别上次同步的点，极大地方便了运维人员，减少出错的几率。binlog-format=ROW #binlog格式log-slave-updates=true #表示如果一个MASTER挂掉的话，另外一个马上接管gtid-mode=on #用于启动GTID及满足附属的其它需求enforce-gtid-consistency=true ##report-port=port #从属服务器的端口#report-host=host #从属服务器的主机名master-info-repository=TABLE #启用此两项，可用于实现在崩溃时保证二进制及从服务器安全的功能relay-log-info-repository=TABLE #sync-master-info=1 #启用之可确保无信息丢失slave-parallel-workers=2 #设定从服务器的SQL线程数；0表示关闭多线程复制功能binlog-checksum=CRC32 #master-verify-checksum=1 #slave-sql-verify-checksum=1 #启用复制有关的所有校验功能binlog-rows-query-log_events=1 ###MySQL 5.6.10版本提供了更方便的基于GTID的复制功能，MySQL可以通过GTID自动识别上次同步的点，极大地方便了运维人员，减少出错的几率。expire_logs_day=5 #超过5天的binlog删除max_binlog_size=104857600#replicate-ignore-db = mysql #忽略不同步主从的数据库#replicate-ignore-db = information_schema#replicate-ignore-db = performance_schema#replicate-ignore-db = test#replicate-ignore-db = zabbix#replicate-ignore-db = sysbench#replicate-ignore-db = db_3307##slave-skip-errors=0 #主从忽略错误数##注释掉,使用默认设置#skip-external-locking##innodb_force_recovery = 1key_buffer_size = 256Mmax_allowed_packet = 100M#table_open_cache = 1024#sort_buffer_size = 128M#net_buffer_length = 8K#read_buffer_size = 128M#read_rnd_buffer_size = 256M#myisam_sort_buffer_size = 32M#character-set-server = utf8skip-name-resolvemax_connections = 10000##慢查询设置slow-query-log=onlong_query_time = 2#log-queries-not-using-indexes##注释掉,使用默认设置## For InnoDBinnodb_buffer_pool_size = 1G#innodb_additional_mem_pool_size = 128M## Set .._log_file_size to 25 % of buffer pool size#innodb_log_file_size = 128M#innodb_log_buffer_size = 8Minnodb_flush_log_at_trx_commit = 1#innodb_lock_wait_timeout = 50#innodb_file_per_table=1[mysqld3]basedir = /usr/local/mysqlcharacter-set-server = utf8port = 3308socket = /tmp/mysql_3308.sockdatadir = /usr/local/mysql/data/dbdata_3308pid-file = /usr/local/mysql/data/dbdata_3308/mysql.pidlog-error = /usr/local/mysql/data/dbdata_3308/mysql.errserver-id = 1skip-character-set-client-handshake #忽略应用程序想要设置的其他字符集init-connect='SET NAMES utf8' #连接时执行的SQLcharacter-set-server=utf8 #服务端默认字符集wait_timeout=1800 #请求的最大连接时间interactive_timeout=1800 #和上一参数同时修改才会生效sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES #sql模式log-bin = mysql-bin #打开二进制功能,MASTER主服务器必须打开此项relay-log = mysql-bin #文件名格式relay-log-index = mysql-bin.index #index文件名#MySQL 5.6.10版本提供了更方便的基于GTID的复制功能，MySQL可以通过GTID自动识别上次同步的点，极大地方便了运维人员，减少出错的几率。binlog-format=ROW #binlog格式log-slave-updates=true #表示如果一个MASTER挂掉的话，另外一个马上接管gtid-mode=on #用于启动GTID及满足附属的其它需求enforce-gtid-consistency=true ##report-port=port #从属服务器的端口#report-host=host #从属服务器的主机名master-info-repository=TABLE #启用此两项，可用于实现在崩溃时保证二进制及从服务器安全的功能relay-log-info-repository=TABLE #sync-master-info=1 #启用之可确保无信息丢失slave-parallel-workers=2 #设定从服务器的SQL线程数；0表示关闭多线程复制功能binlog-checksum=CRC32 #master-verify-checksum=1 #slave-sql-verify-checksum=1 #启用复制有关的所有校验功能binlog-rows-query-log_events=1 ###MySQL 5.6.10版本提供了更方便的基于GTID的复制功能，MySQL可以通过GTID自动识别上次同步的点，极大地方便了运维人员，减少出错的几率。expire_logs_day=5 #超过5天的binlog删除max_binlog_size=104857600#replicate-ignore-db = mysql #忽略不同步主从的数据库#replicate-ignore-db = information_schema#replicate-ignore-db = performance_schema#replicate-ignore-db = test#replicate-ignore-db = zabbix#replicate-ignore-db = sysbench#replicate-ignore-db = db_3308##slave-skip-errors=0 #主从忽略错误数##注释掉,使用默认设置#skip-external-locking##innodb_force_recovery = 1key_buffer_size = 256Mmax_allowed_packet = 100M#table_open_cache = 1024#sort_buffer_size = 128M#net_buffer_length = 8K#read_buffer_size = 128M#read_rnd_buffer_size = 256M#myisam_sort_buffer_size = 32M#character-set-server = utf8skip-name-resolvemax_connections = 10000##慢查询设置slow-query-log=onlong_query_time = 2#log-queries-not-using-indexes##注释掉,使用默认设置## For InnoDBinnodb_buffer_pool_size = 1G#innodb_additional_mem_pool_size = 128M## Set .._log_file_size to 25 % of buffer pool size#innodb_log_file_size = 128M#innodb_log_buffer_size = 8Minnodb_flush_log_at_trx_commit = 1#innodb_lock_wait_timeout = 50#innodb_file_per_table=1[mysqldump]quickmax_allowed_packet = 256M[mysql]no-auto-rehashprompt=\\u@\\d \\R:\\m># Remove the next comment character if you are not familiar with SQL#safe-updates[myisamchk]key_buffer_size = 512Msort_buffer_size = 512Mread_buffer = 8Mwrite_buffer = 8M[mysqlhotcopy]interactive-timeout十二、启动所有实例
/usr/local/mysql/bin/mysqld_multi start 1
/usr/local/mysql/bin/mysqld_multi start 2
/usr/local/mysql/bin/mysqld_multi start 3
登录MySQL多实例
mysql -uroot -S /tmp/mysql_3306.sock
mysql -uroot -S /tmp/mysql_3307.sock
mysql -uroot -S /tmp/mysql_3308.sock
并且修改各自的密码为my.cnf配置档中[mysqld_multi]中指定的密码。
root@(none) 16:09>grant all privileges on *.* to root@'localhost' identified by 'password';root@(none) 16:09>grant all privileges on *.* to root@'127.0.0.1' identified by 'password';root@(none) 16:09>grant all privileges on *.* to root@'%' identified by 'password';其实这里有一点问题，就是[mysqld_multi]中设置的账户，其实它是用来管理mysqld_multi(多实例进程的启动与关闭)的，如果这边的账户和当前库(实例中的所有库)的账户不匹配，则会发生一些奇怪的问题，例如你可以开启这个mysql多实例进程，但无法关闭，甚至是kill ${PID}之后它又会重新启动。
那么，以后就可以通过下面方式来连接MySQL了
mysql -uroot -ppassword -S /tmp/mysql_3306.sockmysql -uroot -ppassword -S /tmp/mysql_3307.sockmysql -uroot -ppassword -S /tmp/mysql_3308.sock 1 0
- mysql 多实例案例实战
- Mysql介绍及多实例配置实战
- mysql索引实例----第二章实战篇
- 实战mycat读写分离和分库分表(1)-本地mysql多实例搭建
- mysql 多表查询案例
- 移动端案例、web前端项目实战(HTML5+css3)、webApp实例源码
- mysql分库分表实战及php代码操作完整实例
- mysql分库分表实战及php代码操作完整实例
- mysql分库分表实战及php代码操作完整实例
- mysql分库分表实战及php代码操作完整实例
- mysql分库分表实战及php代码操作完整实例
- mysql分库分表实战及php代码操作完整实例
- mysql分库分表实战及php代码操作完整实例
- mysql分库分表实战及php代码操作完整实例
- seo实战案例
- 指针--实战案例
- block实战案例
- SpringMVC+Mybatis实战案例
- poj3083 dfs+bfs一次ac
- Exception in thread "main" java.lang.UnsatisfiedLinkError: org.apache.hadoop.io.nativeio.NativeIO$Wi
- AFNetWorking出现code=-1016错误解决办法
- 关于ant自动化打包
- matlab2014a + win764bit + vs2013混合编程（.m转成dll供C++调用）
- mysql 多实例案例实战
- 如何将Birt源码添加到Eclipse中
- PHP获取QQ音乐的API类,可以集成到各种音乐网站上使用
- springmvc配置详解2大集合转载
- mongoDB----Java的基本操作
- DATASET遍历方法
- 感悟：学习做人的道理
- L2 ingress 流程
- nodeJS实现图片上传和展示
