MySQL主从配置

本例使用MySQL多实例进行配置。http://www.cnblogs.com/g120992880/p/mysqld_multi.html

一、修改my.cnf

在相应的[mysqldN]下添加server-id与 log-bin,从节点可以不启用二进制日志。

此处设定3307为主库,3308为从库。

[root@localhost ~]# cat /etc/my.cnf

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user       = multi_admin
password   = my_password
log        = /var/log/mysqld_multi.log

[mysqld3307]
port       = 3307
datadir    = /var/lib/mysql/3307
socket     = /var/lib/mysql/3307/mysql.sock
pid-file   = /var/lib/mysql/3307/mysqld.pid
server-id  = 1
log-bin    = mysql-bin
#user       = unix_user1

[mysqld3308]
port       = 3308
datadir    = /var/lib/mysql/3308
socket     = /var/lib/mysql/3308/mysql.sock
pid-file   = /var/lib/mysql/3308/mysqld.pid
server-id  = 2
log-bin    = mysql-bin
#user       = unix_user1

[mysqld_safe]
log-error=/var/log/mysqld.log

 二、主库中添加一个用户,专门用来从库连接主库进行复制操作

[root@localhost ~]# mysql -u root -P 3307 -h 127.0.0.1 -p
mysql> grant replication slave on *.* to 'replication'@'127.0.0.1' identified by 'replication_pwd';
mysql> flush privileges;

三、查看主库二进制日志与定位点

[root@localhost ~]# mysql -u root -P 3307 -h 127.0.0.1 -p
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      106 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

从上述可确定二进制文件名mysql-bin.000004,定位点106。

四、进入从库，添加连接主库信息

[root@localhost 3307]# mysql -u root -P 3308 -h 127.0.0.1 -p
mysql> CHANGE MASTER TO
    -> MASTER_HOST='127.0.0.1',
    -> MASTER_PORT=3307
    -> MASTER_USER='replication',
    -> MASTER_PASSWORD='replication_pwd',
    -> MASTER_LOG_FILE='mysql-bin.000004',
    -> MASTER_LOG_POS=106;

mysql> start slave;

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: replication
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 106
Relay_Log_File: mysqld-relay-bin.000009
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000004
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: 106
Relay_Log_Space: 552
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)

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

这二项必要是运行状态,如未能正常运行查看日志/val/log/mysqld.log。

最后进入主库进行测试。