Mysql主从复制，单台服务器上实施
- WBOYOriginal
- 2016-06-07 16:36:161302browse
原文链接：?http://blog.csdn.net/songxixi/article/details/8737555 现在公司向在一台服务器上做主从复制，现在了解到的是需要安装多个mysql不同的服务，才可以，我现在 在现有单实例数据库下，分了不同的端口以下所示红色部分；[root@mysql ~]# netstat -t
原文链接：?http://blog.csdn.net/songxixi/article/details/8737555
现在公司向在一台服务器上做主从复制，现在了解到的是需要安装多个mysql不同的服务，才可以，我现在 在现有单实例数据库下，分了不同的端口以下所示红色部分；
[root@mysql ~]# netstat -tunlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:1001 0.0.0.0:* LISTEN 2515/rpc.statd
tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN 2483/portmap
tcp 0 0 127.0.0.1:631 0.0.0.0:* LISTEN 2750/cupsd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 3056/sendmail: acce
tcp 0 0 :::3306 :::* LISTEN 4025/mysqld
tcp 0 0 :::3307 :::* LISTEN 17423/mysqld
tcp 0 0 :::3308 :::* LISTEN 17388/mysqld
tcp 0 0 :::3309 :::* LISTEN 20371/mysqld
tcp 0 0 :::22 :::* LISTEN 2741/sshd
udp 0 0 0.0.0.0:995 0.0.0.0:* 2515/rpc.statd
udp 0 0 0.0.0.0:998 0.0.0.0:* 2515/rpc.statd
udp 0 0 0.0.0.0:42601 0.0.0.0:* 3204/avahi-daemon:
udp 0 0 0.0.0.0:5353 0.0.0.0:* 3204/avahi-daemon:
udp 0 0 0.0.0.0:111 0.0.0.0:* 2483/portmap
udp 0 0 0.0.0.0:631 0.0.0.0:* 2750/cupsd
udp 0 0 :::5353 :::* 3204/avahi-daemon:
udp 0 0 :::59254 :::* 3204/avahi-daemon:
[root@mysql mysql]# mysqld_multi --defaults-extra-file=/etc/mysqld_multi.cnf report
Reporting MySQL servers
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
MySQL server from group: mysqld4 is running
[root@mysql mysql]# lsof -i:3308
COMMAND PID USER FD TYPE DEVICE SIZE NODE NAME
mysqld 17388 mysql 11u IPv6 45429 TCP *:tns-server (LISTEN)
[root@mysql mysql]# lsof -i:3307
COMMAND PID USER FD TYPE DEVICE SIZE NODE NAME
mysqld 17423 mysql 11u IPv6 45479 TCP *psession-prxy (LISTEN)
[root@mysql mysql]# lsof -i:3309
COMMAND PID USER FD TYPE DEVICE SIZE NODE NAME
mysqld 20371 mysql 11u IPv6 47851 TCP *:tns-adv (LISTEN)
[root@mysql mysql]# lsof -i:3306
COMMAND PID USER FD TYPE DEVICE SIZE NODE NAME
mysqld 4025 mysql 11u IPv6 16575 TCP *:mysql (LISTEN)
[root@mysql mysql]#
[root@mysql ~]#
在以上服务都启动正常的情况下，配置主从，我没有调整IO的线程，不知道在不同端口见做主从复制关系，以下是我配置最后遇到的错误，请绿林好汉帮忙看看啊，拍砖。。。
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.10
Master_User: slave001
Master_Port: 3308
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1027
Relay_Log_File: localhost3308-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: No
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: 1027
Relay_Log_Space: 107
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1593
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
我在mysqld_multi.cnf里面添加了以下红色的，复制内容启动还是报错，请指点哇
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = multi_admin
password = my_password
[mysqld2]
socket = /tmp/mysql3307.sock
port = 3307
pid-file = /usr/local/mysql/data3307/localhost3307.pid
datadir = /usr/local/mysql/data3307
#language = /usr/local/mysql/share/mysql/english
user = mysql
[mysqld3]
#mysqld = /path/to/mysqld_safe
#ledir = /path/to/mysqld-binary/
#mysqladmin = /path/to/mysqladmin
socket = /tmp/mysql3308.sock
port = 3308
pid-file = /usr/local/mysql/data3308/localhost3308.pid
datadir = /usr/local/mysql/data3308
#language = /usr/local/mysql/share/mysql/swedish
user = mysql
master-host = 192.168.1.10
master-user = slave001
master-password = slave001
master-port = 3306
replicate-do-db=test
错误：
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.10
Master_User: slave001
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000018
Read_Master_Log_Pos: 107
Relay_Log_File: localhost3308-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000018
Slave_IO_Running: No
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: 107
Relay_Log_Space: 107
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1593
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
哎，经过几个小时的琢磨，终于看到庐山真面目了！以下是成功启动的效果；
[root@mysql ~]# mysqld_multi --defaults-extra-file=/etc/my.cnf report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
MySQL server from group: mysqld4 is running
[root@mysql ~]# mysqld_multi --defaults-extra-file=/etc/my.cnf report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
MySQL server from group: mysqld4 is running
[root@mysql ~]# mysqld_multi --defaults-extra-file=/etc/my.cnf report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
MySQL server from group: mysqld4 is running
[root@mysql ~]# mysqld_multi --defaults-extra-file=/etc/my.cnf report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
MySQL server from group: mysqld4 is running
[root@mysql ~]#
其实，在我配置muti的时候有些参数没有做好设置，到时server-id服务启动不起来，我把所有的muti的mysqld【1-4】全部放到我们的配置文件内/etc/my.cnf，包括我们的3306端口的实例库，在启动的时候一起跑即可！以下是我my.cnf的配置文件信心，请查阅；
# The following options will be passed to all MySQL clients
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = root
# Here follows entries for some specific programs
# The MySQL server
[mysqld1]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
user=mysql
old_passwords=1
port=3306
skip-name-resolve
server_id=1
log-bin=mysql-bin
max_connections=1000
key_buffer_size=218348
query_cache_size=10
read_rnd_buffer_size=134228
table_cache=10000
tmp_table_size=5362
log-slow-queries=slow.log
long_query_time=1
concurrent_insert=2
thread_cache_size=300
#log=/usr/local/mysql/data/mysql1.log
pid-file=/usr/local/mysql/data/mysql1.pid
log-error=/usr/local/mysql/data/log.1
log-slave-updates
slave-skip-errors=ALL
[mysqld2]
datadir=/usr/local/mysql/data3307
socket=/tmp/mysql3307.sock
user=mysql
old_passwords=1
skip-name-resolve
server_id=222
log-bin=mysql-bin
max_connections=1000
key_buffer_size=218348
query_cache_type=0
read_rnd_buffer_size=1342128
table_cache=10000
tmp_table_size=5368912
log-slow-queries=slow.log
long_query_time=1
concurrent_insert=2
thread_cache_size=300
port=3307
pid-file=/usr/local/mysql/data3307/localhost3307.pid
log-error=/usr/local/mysql/data3307/log.2
[mysqld3]
datadir=/usr/local/mysql/data3308
socket=/tmp/mysql3308.sock
user=mysql
old_passwords=1
skip-name-resolve
server_id=223
log-bin=mysql-bin
max_connections=1000
key_buffer_size=218348
query_cache_type=0
read_rnd_buffer_size=1342128
table_cache=10000
tmp_table_size=5368912
log-slow-queries=slow.log
long_query_time=1
concurrent_insert=2
thread_cache_size=300
port=3308
pid-file=/usr/local/mysql/data3308/localhost3308.pid
log-error=/usr/local/mysql/data3308/log.3
[mysqld4]
datadir=/usr/local/mysql/data3309
socket=/tmp/mysql3309.sock
user=mysql
old_passwords=1
skip-name-resolve
server_id=224
log-bin=mysql-bin
max_connections=1000
key_buffer_size=218348
query_cache_type=0
read_rnd_buffer_size=1342128
table_cache=10000
tmp_table_size=5368912
log-slow-queries=slow.log
long_query_time=1
concurrent_insert=2
thread_cache_size=300
port=3309
pid-file=/usr/local/mysql/data3309/localhost3309.pid
log-error=/usr/local/mysql/data3309/log.4
以上就是配置的启动服务内容，我把server-id分到每个mysqld内部，并保持唯一即可，实现我们要做的单台服务器做主从复制的基本配置要求；配置主从的步骤很简单我就不在这里说了，有不了解如何做主从的可以随时留言即可，以下为我配置成功后主从的启动参数的效果；
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.10
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000032
Read_Master_Log_Pos: 893
Relay_Log_File: localhost3307-relay-bin.000003
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000032
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: 893
Relay_Log_Space: 1194
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
原文地址：Mysql主从复制，单台服务器上实施, 感谢原作者分享。
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：My sql 5.6新特性深入剖析innodb引擎Next article：Scaling Big Data Mining Infrastructure at Twitter