Single-machine multi-instance MySQL master-slave replication

There is time to write today, otherwise the heart is always a bit uncomfortable. single-Machine multi-instance has always been the style of the cock silk ...

Experimental environment

RHEL6.5

172.24.0.130 3306

172.24.0.130 3307

01, the use of the MySQL binary rapid installation

：

https://dev.mysql.com/downloads/mysql/

Http://pan.baidu.com/s/1qYI0ybq

Http://pan.baidu.com/s/1pLHM2TH Password: pg5t

#操作

#根据需求选择对应的版本

02. Unzip and set up the data directory

Tar zxf xxx.tar.gz-c/tmp

mkdir-p {data_3306,data3307} #存放数据

mkdir conf #放my. cnf

03. Modify the configuration file my.cnf

#3306my. cnf

[Client]default-character-Set=UTF8 #客户端连接编码 [Mysql]auto-Rehash #客户端tab补全default-character-Set=UTF8 #编码 [mysqld]
user=mysql #运行账户port =3306 #定义端口Log-bin=mysql-bin #开启二进制日志server-id=001 #定义服务IDMax_allowed_packet=50M # # #最大wait_timeout=3600# # #等待超时默认sinteractive_timeout=3600# # #活动超时innodb_buffer_pool_size=128mjoin_buffer_size=128msort_buffer_size=2mread_rnd_buffer_size=2Mbasedir =/tmp/mysql_mulit #MYSQL根目录datadir =/tmp/mysql_mulit/data_3306 #MYSQL数据存放目录socket =/tmp/ 3306_mysql.sock #套接字init_connect='SET collation_connection = utf8_unicode_ci'Init_connect='SET NAMES UTF8'character-set-server= UTF8 #定义数据库默认字符 server\collationcollation-server=Utf8_unicode_ciskip-character-Set-client-Handshakesymbolic-links=0Sql_mode=No_engine_substitution,strict_trans_tables
Lower_case_table_names = 0 #对表的大小写不敏感
[Mysqld_safe] Log -error=/tmp/3306_err.log #错误日志pid-file=/tmp/mysql_mulit/data_3306/mysqld.pid #pid, Multi-protocol communication Tcp/sock

3307my.cnf

[Client]default-character-Set=UTF8 #客户端连接编码 [Mysql]auto-Rehash #客户端tab补全default-character-Set=UTF8 #编码 [mysqld]
User=mysqlport=3307#定义端口log-bin=mysql-bin #开启二进制日志server-id=011#定义服务IDmax_allowed_packet=50M # # #最大wait_timeout=3600# # #等待超时默认sinteractive_timeout=3600# # #活动超时innodb_buffer_pool_size=128mjoin_buffer_size=128msort_buffer_size=2mread_rnd_buffer_size=2Mbasedir=/tmp/Mysql_mulit #MYSQL根目录datadir=/tmp/mysql_mulit/data_3307 #MYSQL数据存放目录socket=/tmp/3307_mysql.sock #套接字init_connect='SET collation_connection = utf8_unicode_ci'Init_connect='SET NAMES UTF8'character-Set-server=UTF8 #定义数据库默认字符 server\collationcollation-server=Utf8_unicode_ciskip-character-Set-client-Handshakesymbolic-links=0Sql_mode=no_engine_substitution,strict_trans_tables lower_case_table_names=0#对表的大小写不敏感 [Mysqld_safe]log-error=/tmp/3307_err.log #错误日志pid-file=/tmp/mysql_mulit/data_3307/mysqld.pid #pid, Multi-Protocol communication Tcp/sock

04. Initialize the Database

./scripts/mysql_install_db--DEFAULTS-FILE=CONF/3306MY.CNF

./scripts/mysql_install_db--DEFAULTS-FILE=CONF/3306MY.CNF

05. Start the Database

./bin/mysqld_safe--DEFAULTS-FILE=CONF/3306MY.CNF &

./bin/mysqld_safe--DEFAULTS-FILE=CONF/3307MY.CNF &

06. Initialize Root Password

./bin/mysqladmin-p 3306-u root password ' 123123 '

./bin/mysqladmin-p 3307-u root password ' 123123 '

07, Master host settings (3306)

Create a copy account from

Grant Replication Slave on * * to ' mysync ' @ '% ' identified by ' 123456 '; #所有IP
Grant Replication Slave on * * to ' mysync ' @ ' localhost ' identified by ' 123456 '; #127.0.0.1
Grant Replication Slave on * * to ' mysync ' @ ' ZABIBX ' identified by ' 123456 '; #172.24.0.130, Zabbix is host name

View master Information

Mysql> Show master status; #目的跟slave对上口径
+------------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |              541 |                  |                   | |
+------------------+----------+--------------+------------------+-------------------+

08. Slave from host settings (3307)

Enter MySQL to establish a connection with master host

Change Master to master_host= ' 172.24.0.130 ', master_port=3306,master_user= ' Mysync ', master_password= ' 123456 ', master _log_file= ' mysql-bin.000003 ', master_log_pos=541;

show slave status\g;

start slave; #启动从主机stop slave; stop from the host

#中两个yes说明从机已经连接到主机

Single-machine multi-instance MySQL master-slave replication