双十一过后,我从一个光棍,变成了一个负债累累的光棍。

数据库主从模式设置

 

 

Mysql主从模式

Master:

1> 授权用户相应权限

 

 

mysql> grant replication slave on *.* to 'copy'@'192.168.174.%' identified by 'mima23333232';
Query OK, 0 rows affected (0.00 sec)


 

2> 查看master状态值,用于slave库上同步

 

 

mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 329
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)


 

Slave1:

 

1> 使用change master语句,确定master库的ip、用户、密码等信息

 

 

mysql> change master to
-> master_host='192.168.174.164',
-> master_user='lxq',
-> master_password='123456',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=329;


 

Query OK, 0 rows affected, 2 warnings (0.00 sec)

以下两项是从master status中获取的File和Position与下列相对应

master_log_file='mysql-bin.000001'

master_log_pos=329

2> 开启同步

 

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)


 

 

3> 验证Slave_IO_Running、Slave_SQL_Running是否是YES,两个都是YES表示mysql主从复制配置成功了。

 

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.174.164
Master_User: lxq
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 329
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes


 

Slave_IO_Running: Yes(负责从库去主库读取二进制日志,并写入到从库的中继日志)

 

Slave_SQL_Running: Yes(负责将中继日志准换成SQL语句后执行)

 

备注:Slave2库重复一下Slave1的操作即可完成一主多从

 

3.3 验证能否成功同步

 

测试主从是否同步(master数据库上执行Sql、Slave上面去验证)

 

Master:

 

mysql> create database lxqdb;
标签

最新评论