美文网首页
mysql主从配置

mysql主从配置

作者: 脉醉 | 来源:发表于2017-04-17 09:13 被阅读0次

假设 主服务器地址 10.1.1.108 从服务器地址 10.1.1.161

1、主服务器

(1).建立数据库

mysql> CREATE DATABASE `test_db` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Query OK, 1 row affected (0.00 sec)

(2).修改my.cnf文件

在[mysqld]中增加

#for master and slave

server-id = 1

log-bin = mysql-bin

binlog-do-db = test_db

binlog-ignore-db = mysql

(3).对从库新建用户

mysql> grant replication slave on *.* to 'root'@'10.1.1.161' identified by '';

Query OK, 0 rows affected (0.00 sec)

(4).重启mysql

$ sudo service mysql restart

(5).查看master状态

mysql> show master status\G;

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

File: mysql-bin.000001

Position: 106

Binlog_Do_DB: test_db

Binlog_Ignore_DB: mysql

1 row in set (0.00 sec)

ERROR:

No query specified

2、从服务器

(1).建立数据库

mysql> CREATE DATABASE `test_db` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Query OK, 1 row affected (0.00 sec)

(2).修改my.cnf文件

在[mysqld]中增加

#for master and slave

server-id = 2

binlog-do-db = test_db

(3).重启mysql

$ sudo service mysql restart

(4).将从服务器指向主服务器

mysql> slave stop;

Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_host='10.1.1.108', master_user='root', master_password='', master_log_file='mysql-bin.000001',master_log_pos=106;

Query OK, 0 rows affected (0.01 sec)

mysql>

mysql> slave start;

Query OK, 0 rows affected (0.00 sec)

(5).重启mysql,查看运行状态

$ sudo service mysql restart

mysql> show slave status \G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 10.1.1.108

Master_User: root

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 106

Relay_Log_File: mysqld-relay-bin.000004

Relay_Log_Pos: 251

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

ERROR:

No query specified

注意:Slave_IO_Running 和Slave_SQL_Running都为Yes才算设置正确

相关文章

网友评论

      本文标题:mysql主从配置

      本文链接:https://www.haomeiwen.com/subject/szgzattx.html