这里我使用单台服务器上的两个mysql实例进行搭建，主要用到了mysql自带的mysqld_multi
这里我使用单台服务器上的两个mysql实例进行搭建，主要用到了mysql自带的mysqld_multi
一、复制原理
开始搭建前有个mysql复制原理的基础知识需要补充:
mysql进行主备复制使用到了三个线程：
1.主库上的转存储线程:
会将mysql server提交的事务写入到二进制文件中，这个二进制文件就叫做binlog。
2.备库上的连接线程：
备库启动后，负责和主库通信，读取binlog，同时，将binlog存储进自己的一个叫中继日志的relaylog中。
3.备库上的relaylog重放线程:
此线程会将relaylog中的事件在备库上进行回放，说白点就是重新执行一次
二、搭建步骤
1./etc新增文件mysqld_multi.cnf
将/user/share/mysql/my-innodb-heavy-4g.cnf文件复制到/etc，重新命名为xxx.cnf（任何你想要的名字）
在配置文件中新增三个实例
--------------------------------------------------------------------------------
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = multi_admin
password = multipass
log = /var/log/mysqld_multi.log
[mysqld3307]
port = 3307
pid-file = /var/lib/mysql3307/mysql3307.pid
socket = /var/lib/mysql3307/mysql3307.sock
datadir=/var/lib/mysql3307
user=mysql
set-variable=max_connections=27000
log_bin = mysql-bin
server_id = 3307
[mysqld3308]
port = 3308
pid-file = /var/lib/mysql3308/mysql3308.pid
socket = /var/lib/mysql3308/mysql3308.sock
datadir=/var/lib/mysql3308
user=mysql
set-variable=max_connections=28000
log_bin = mysql-bin
server_id = 3308
relay_log = /var/lib/mysql3308/mysql-relay-bin
log_slave_updates = 1
read_only = 1
[mysqld3309]
port = 3309
pid-file = /var/lib/mysql3309/mysql3309.pid
socket = /var/lib/mysql3309/mysql3309.sock
datadir=/var/lib/mysql3309
user=mysql
set-variable=max_connections=29000
log_bin = mysql-bin
server_id = 3309
relay_log = /var/lib/mysql3309/mysql-relay-bin
log_slave_updates = 1
read_only = 1
--------------------------------------------------------------------------------
这里我会将将mysqld3307这个实例做为主库，mysqld3308和mysql3309这个实例作为备库
2.做好了配置之后开启两个实例：
mysqld_multi --defaults-file=/etc/mysql/mysqld_muti.cnf start
3.开启复制前主库的准备工作:
1.在主库上增加一个复制账号：
使用sock文件登陆mysql:
mysql -uroot -p -s /var/lib/mysql3307/mysql3307.sock（这个套接字文件还记得吗，是在之前定义实例的时候定义的）
--------------------------------------------------------------------------------
mysql>grant replication slave,replication client on *.* to replication@'localhost' identified by 'replication';
mysql>flush privileges;
--------------------------------------------------------------------------------
查看主库上的binlog是否开启:
--------------------------------------------------------------------------------
mysql> show master status;
+------------------+----------+--------------+------------------+
| file | position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 1001 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
--------------------------------------------------------------------------------
4.开启复制:
登陆到备库：
mysql -uroot -p -s /var/lib/mysql3308/mysql3308.sock
--------------------------------------------------------------------------------
change master to master_host = localhost,
master_user = 'replication',
master_password = 'replication',（你之前在主库上创建复制账号时指定的）
master_port = 3306;
start slave;
查看复制是否开始工作:
--------------------------------------------------------------------------------
mysql> show slave status \g;
*************************** 1. row ***************************
slave_io_state: waiting for master to send event
master_host: localhost
master_user: replication
master_port: 3307
connect_retry: 60
master_log_file: mysql-bin.000001
read_master_log_pos: 622
relay_log_file: mysql-relay-bin.000002
relay_log_pos: 767
relay_master_log_file: mysql-bin.000001
slave_io_running: yes
slave_sql_running: yes
replicate_do_db:
replicate_ignore_db:
replicate_do_table:
replicate_ignore_table:
replicate_wild_do_table:
replicate_wild_ignore_table:
last_errno: 0
last_error:
skip_counter: 0
exec_master_log_pos: 622
relay_log_space: 922
until_condition: none
until_log_file:
until_log_pos: 0
master_ssl_allowed: no
master_ssl_ca_file:
master_ssl_ca_path:
master_ssl_cert:
master_ssl_cipher:
master_ssl_key:
seconds_behind_master: 0
master_ssl_verify_server_cert: no
last_io_errno: 0
last_io_error:
last_sql_errno: 0
last_sql_error:
1 row in set (0.00 sec)