mysql主从配置笔记_mysql主从配置

1. 在master上创建复制用户

进入mysql环境，执行以下mysql语句：

mysql> create user repl_user;

Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'backup'@'192.168.1.145'identified by '123456';

Query OK, 0 rows affected (0.00 sec)

IP地址是从服务器的地址，backup是master给slave的用户名。

2.  在master上开启二进制日志

vim /etc/my.cnf

[mysqld]

log-bin=master-bin

log-bin-index=master-bin.index

server-id=1

server-id不可与从服务器的server-id重复。

3.  配置slave服务器

vim /etc/my.cnf

[mysqld]

server-id=2

relay-log=slave-relay-bin

relay-log-index=slave-relay-bin.index

这三行的顺序必须如上。

4.  进入slave的mysql控制台进行主从设置

mysql> change master to

-> master_host = '192.168.1.144',

-> master_port = 3306,

-> master_user = 'backup',

-> master_password = '123456';

Query OK, 0 rows affected (0.10 sec)

master_host是主服务器的IP地址。

5.  开启从服务器

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

6.  查看从服务器的状态

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State:Waiting for master to send event

Master_Host: 192.168.1.144

Master_User:backup

Master_Port:3306

Connect_Retry:60

Master_Log_File:master-bin.000001

Read_Master_Log_Pos:106

Relay_Log_File:slave-relay-bin.000002

Relay_Log_Pos:252

Relay_Master_Log_File:master-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:106

Relay_Log_Space: 407

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.00sec)

如果这两项显示OK就说明配置成功。