Mysql应用基于MySQL Master Slave同步配置的操作详解

发布时间：2020-12-12 02:27:32 所属栏目：MySql教程来源：网络整理

导读：《Mysql应用基于MySQL Master Slave同步配置的操作详解》要点：本文介绍了Mysql应用基于MySQL Master Slave同步配置的操作详解，希望对您有用。如果有疑问，可以联系我们。环境： PC：ubuntu 10.10? 192.168.1.112(master) 192.168.10.245(slave) MySQL :

《Mysql应用基于MySQL Master Slave同步配置的操作详解》要点：
本文介绍了Mysql应用基于MySQL Master Slave同步配置的操作详解，希望对您有用。如果有疑问，可以联系我们。

环境：
PC：ubuntu 10.10? 192.168.1.112(master) 192.168.10.245(slave)
MySQL : 5.1.49-1ubuntu8.1-log
在master中已经存在数据库test
首先改动mysql配置文件：/etc/mysql/my.cnf
[master]
#author:zhxia
代码如下:
?#master 同步设置
?server-id?????????????? = 1
?log_bin???????????????? = /var/log/mysql/mysql-test-bin.log
?expire_logs_days??????? = 10
?max_binlog_size???????? = 100M
?binlog_format?????????? =mixed

[slave]
#author:zhxia
代码如下:
server-id?????????????? = 2
?replicate-do-db=test
?replicate-do-db=blog
?log_bin???????????????? = /var/log/mysql/mysql-bin.log
?relay_log?????????????? =/var/log/mysql/mysql-relay-bin.log
?expire_logs_days??????? = 10
?max_binlog_size???????? = 100M
?#binlog_do_db?????????? = test
?#binlog_ignore_db?????? = include_database_name
?binlog_format?????????? = mixed
?slave-net-timeout=6012 master-connect-retry=10

接着在master上创立备份帐号
代码如下:
grant replication slave,replication client on *.* to 'slave'@'192.168.10.245' identified by '123456';

将master中的数据库导入到slave中,
先锁表,禁止写入操作
flush tables with read lock;
先从master导出：mysqldump -uroot -p test > /tmp/test.sql
再导入到slave： mysql -uroot -p test < /tmp/test.sql,记得必要先建库test
进入master上的mysql,查看master状态

#author:zhxia
代码如下:
mysql> show master status;
?+-----------------------+----------+--------------+------------------+
?| File????????????????? | Position | Binlog_Do_DB | Binlog_Ignore_DB |
?+-----------------------+----------+--------------+------------------+
?| mysql-test-bin.000022 |????? 624 |????????????? |????????????????? |
?+-----------------------+----------+--------------+------------------+
?1 row in set (0.02 sec)

进入slave上的Mysql
#author:zhxia
代码如下:
change master to? master_host='192.168.1.112',master_user='slave',master_password='123456',master_log_file='mysql-test-bin.000022',master_log_pos=106;

然后启动salve,并查看状态：??
#author:zhxia
代码如下:
start slave;

?mysql> show slave statusG;
?*************************** 1. row ***************************
??????????????? Slave_IO_State: Waiting for master to send event
?????????????????? Master_Host: 192.168.1.112
?????????????????? Master_User: slave
?????????????????? Master_Port: 3306
???????????????? Connect_Retry: 60
?????????????? Master_Log_File: mysql-test-bin.000022
?????????? Read_Master_Log_Pos: 624
??????????????? Relay_Log_File: mysql-relay-bin.000005
???????????????? Relay_Log_Pos: 533
???????? Relay_Master_Log_File: mysql-test-bin.000022
????????????? Slave_IO_Running: Yes
???????????? Slave_SQL_Running: Yes
?????????????? Replicate_Do_DB: test,blog
?????????? 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: 624
?????????????? Relay_Log_Space: 688
?????????????? 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

末了将master上的表解锁
?unlock tables;

欢迎参与《Mysql应用基于MySQL Master Slave同步配置的操作详解》讨论，分享您的想法，编程之家PHP学院为您提供专业教程。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!