lunzi
- 浏览: 93692 次
- 性别:
- 来自: 北京
-
文章分类
最新评论
-
lunzi：
sqllite3 installhttp://www.supe ...
linux架设subversion(svn) -
lunzi：
libiconv.so.2: cannot open shar ...
linux架设subversion(svn)
环境：
master：192.168.2.142
slave ：192.168.2.143
两者都装了mysql 5.6.10
硬件环境都一样，vmware下centos 6.2
内存 1GB
磁盘 20GB
CPU 2个
在master端创建测试数据库
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.10 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database kenyon_master;
Query OK, 1 row affected (0.03 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kenyon_master |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use kenyon_master;
Database changed
mysql> create table t_kenyon(id int);
Query OK, 0 rows affected (0.07 sec)
mysql> insert into t_kenyon values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t_kenyon;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql> show master status;
Empty set (0.00 sec)
mysql> \q
Bye
[root@localhost ~]#
修改master端/etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id =1
binlog-do-db=kenyon_master
binlog-ignore-db=mysql,test,information_schema,performance_schema
bind-address = 0.0.0.0
innodb_data_home_dir = /usr/local/mysql/data/
innodb_log_group_home_dir = /usr/local/mysql/data/
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
重启master端DB，使修改生效，并创建slave端连到master端的用户
[root@localhost ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL............ SUCCESS!
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.10-log Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like 'innodb_data_home_dir';
+----------------------+------------------------+
| Variable_name | Value |
+----------------------+------------------------+
| innodb_data_home_dir | /usr/local/mysql/data/ |
+----------------------+------------------------+
1 row in set (0.00 sec)
mysql> show variables like 'innodb_additional_mem_pool_size';
+---------------------------------+----------+
| Variable_name | Value |
+---------------------------------+----------+
| innodb_additional_mem_pool_size | 20971520 |
+---------------------------------+----------+
1 row in set (0.00 sec)
mysql> show master stauts;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'stauts' at line 1
mysql> show master status;
+------------------+----------+---------------+--------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+---------------+--------------------------------------------------+-------------------+
| mysql-bin.000001 | 120 | kenyon_master | mysql,test,information_schema,performance_schema | |
+------------------+----------+---------------+--------------------------------------------------+-------------------+
1 row in set (0.02 sec)
mysql> grant replication slave on *.* to repluser@'192.168.2.143' identified by '123456';
Query OK, 0 rows affected (0.07 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> \q
Bye
[root@localhost ~]#
备份master端数据,并拷贝至slave端
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+---------------+--------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+---------------+--------------------------------------------------+-------------------+
| mysql-bin.000002 | 120 | kenyon_master | mysql,test,information_schema,performance_schema | |
+------------------+----------+---------------+--------------------------------------------------+-------------------+
1 row in set (0.00 sec)
mysql>
另外一个session:
[root@localhost log]# cd /usr/local/mysql/data
[root@localhost data]# tar -zcvf backup.tar.gz kenyon_master
kenyon_master/
kenyon_master/t_kenyon.frm
kenyon_master/t_kenyon.ibd
kenyon_master/db.opt
[root@localhost data]#
[root@localhost data]# scp backup.tar.gz root@192.168.2.143:/usr/local/mysql/data/
The authenticity of host '192.168.2.143 (192.168.2.143)' can't be established.
RSA key fingerprint is 21:85:2a:f7:28:41:61:57:68:6c:3b:47:0a:25:92:47.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.2.143' (RSA) to the list of known hosts.
root@192.168.2.143's password:
backup.tar.gz 100% 887 0.9KB/s 00:00
[root@localhost data]#
回到之前的session,解锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
修改slave端的/etc/my.cnf
[mysqld]
server-id = 2
master-host = 192.168.2.142 #5.5以上废弃
master-user = repluser #5.5以上废弃
master-password = 123456 #5.5以上废弃
master-port = 3306 #5.5以上废弃
master-connect-retry=60 #重试时间60秒, #5.5以上废弃
replicate-do-db=kenyon_master #需要同步的库
log-slave-updates=1 #启用从库日志，这样可以设置链式复制
read-only = 1 #0表示可读写，1表示只读,但是只针对普通用户，超级用户和同步用户不受此限制
重启后查看状态：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.2.142
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 120
Relay_Log_File: localhost-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB: kenyon_master
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: 120
Relay_Log_Space: 120
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
上面有两个参数：Slave_IO_Running， Slave_SQL_Running的状态是NO，说明有问题，网络已经排查，我们重启slave后，发现正常了。
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.10 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.142
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 120
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: kenyon_master
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: 120
Relay_Log_Space: 460
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 707d5ebf-8493-11e2-9e08-000c29202821
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
mysql>
这个时候master端的数据变化可以反映在slave端(在master库里新增删表或者修改数据，在slave端查看是否同步)。
master端的进程
mysql> show processlist\G
*************************** 1. row ***************************
Id: 12
User: root
Host: localhost
db: kenyon_master
Command: Sleep
Time: 1481
State:
Info: NULL
*************************** 2. row ***************************
Id: 20
User: root
Host: localhost
db: kenyon_master
Command: Query
Time: 0
State: init
Info: show processlist
*************************** 3. row ***************************
Id: 23
User: repluser
Host: 192.168.2.143:59811
db: NULL
Command: Binlog Dump
Time: 708
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
3 rows in set (0.02 sec)
slave端的进程
mysql> show processlist\G
*************************** 1. row ***************************
Id: 5
User: root
Host: localhost
db: kenyon_master
Command: Query
Time: 0
State: init
Info: show processlist
*************************** 2. row ***************************
Id: 10
User: system user
Host:
db: NULL
Command: Connect
Time: 602
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 11
User: system user
Host:
db: NULL
Command: Connect
Time: 282
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
3 rows in set (0.03 sec)
至此，一个master-slave的配置已经完成。
change master to master_host='172.168.2.148',master_user='repluser',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=120
如果需要做级联复制，则加一下
log-slave-updates=1
加完这个重启从机，查看是否生效:
mysql> show variables like 'log_slave_%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| log_slave_updates | ON |
+-------------------+-------+
1 row in set (0.00 sec)
配好了从机有可能需要重启一下
如果多个DB复制
binlog-do-db=DB1
binlog-do-db=DB2
binlog-do-db=DB3
replicate-do-db=DB1
replicate-do-db=DB2
replicate-do-db=DB3
master：192.168.2.142
slave ：192.168.2.143
两者都装了mysql 5.6.10
硬件环境都一样，vmware下centos 6.2
内存 1GB
磁盘 20GB
CPU 2个
在master端创建测试数据库
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.10 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database kenyon_master;
Query OK, 1 row affected (0.03 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kenyon_master |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use kenyon_master;
Database changed
mysql> create table t_kenyon(id int);
Query OK, 0 rows affected (0.07 sec)
mysql> insert into t_kenyon values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t_kenyon;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql> show master status;
Empty set (0.00 sec)
mysql> \q
Bye
[root@localhost ~]#
修改master端/etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id =1
binlog-do-db=kenyon_master
binlog-ignore-db=mysql,test,information_schema,performance_schema
bind-address = 0.0.0.0
innodb_data_home_dir = /usr/local/mysql/data/
innodb_log_group_home_dir = /usr/local/mysql/data/
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
重启master端DB，使修改生效，并创建slave端连到master端的用户
[root@localhost ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL............ SUCCESS!
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.10-log Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like 'innodb_data_home_dir';
+----------------------+------------------------+
| Variable_name | Value |
+----------------------+------------------------+
| innodb_data_home_dir | /usr/local/mysql/data/ |
+----------------------+------------------------+
1 row in set (0.00 sec)
mysql> show variables like 'innodb_additional_mem_pool_size';
+---------------------------------+----------+
| Variable_name | Value |
+---------------------------------+----------+
| innodb_additional_mem_pool_size | 20971520 |
+---------------------------------+----------+
1 row in set (0.00 sec)
mysql> show master stauts;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'stauts' at line 1
mysql> show master status;
+------------------+----------+---------------+--------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+---------------+--------------------------------------------------+-------------------+
| mysql-bin.000001 | 120 | kenyon_master | mysql,test,information_schema,performance_schema | |
+------------------+----------+---------------+--------------------------------------------------+-------------------+
1 row in set (0.02 sec)
mysql> grant replication slave on *.* to repluser@'192.168.2.143' identified by '123456';
Query OK, 0 rows affected (0.07 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> \q
Bye
[root@localhost ~]#
备份master端数据,并拷贝至slave端
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+---------------+--------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+---------------+--------------------------------------------------+-------------------+
| mysql-bin.000002 | 120 | kenyon_master | mysql,test,information_schema,performance_schema | |
+------------------+----------+---------------+--------------------------------------------------+-------------------+
1 row in set (0.00 sec)
mysql>
另外一个session:
[root@localhost log]# cd /usr/local/mysql/data
[root@localhost data]# tar -zcvf backup.tar.gz kenyon_master
kenyon_master/
kenyon_master/t_kenyon.frm
kenyon_master/t_kenyon.ibd
kenyon_master/db.opt
[root@localhost data]#
[root@localhost data]# scp backup.tar.gz root@192.168.2.143:/usr/local/mysql/data/
The authenticity of host '192.168.2.143 (192.168.2.143)' can't be established.
RSA key fingerprint is 21:85:2a:f7:28:41:61:57:68:6c:3b:47:0a:25:92:47.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.2.143' (RSA) to the list of known hosts.
root@192.168.2.143's password:
backup.tar.gz 100% 887 0.9KB/s 00:00
[root@localhost data]#
回到之前的session,解锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
修改slave端的/etc/my.cnf
[mysqld]
server-id = 2
master-host = 192.168.2.142 #5.5以上废弃
master-user = repluser #5.5以上废弃
master-password = 123456 #5.5以上废弃
master-port = 3306 #5.5以上废弃
master-connect-retry=60 #重试时间60秒, #5.5以上废弃
replicate-do-db=kenyon_master #需要同步的库
log-slave-updates=1 #启用从库日志，这样可以设置链式复制
read-only = 1 #0表示可读写，1表示只读,但是只针对普通用户，超级用户和同步用户不受此限制
重启后查看状态：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.2.142
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 120
Relay_Log_File: localhost-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB: kenyon_master
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: 120
Relay_Log_Space: 120
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
上面有两个参数：Slave_IO_Running， Slave_SQL_Running的状态是NO，说明有问题，网络已经排查，我们重启slave后，发现正常了。
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.10 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.142
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 120
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: kenyon_master
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: 120
Relay_Log_Space: 460
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 707d5ebf-8493-11e2-9e08-000c29202821
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
mysql>
这个时候master端的数据变化可以反映在slave端(在master库里新增删表或者修改数据，在slave端查看是否同步)。
master端的进程
mysql> show processlist\G
*************************** 1. row ***************************
Id: 12
User: root
Host: localhost
db: kenyon_master
Command: Sleep
Time: 1481
State:
Info: NULL
*************************** 2. row ***************************
Id: 20
User: root
Host: localhost
db: kenyon_master
Command: Query
Time: 0
State: init
Info: show processlist
*************************** 3. row ***************************
Id: 23
User: repluser
Host: 192.168.2.143:59811
db: NULL
Command: Binlog Dump
Time: 708
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
3 rows in set (0.02 sec)
slave端的进程
mysql> show processlist\G
*************************** 1. row ***************************
Id: 5
User: root
Host: localhost
db: kenyon_master
Command: Query
Time: 0
State: init
Info: show processlist
*************************** 2. row ***************************
Id: 10
User: system user
Host:
db: NULL
Command: Connect
Time: 602
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 11
User: system user
Host:
db: NULL
Command: Connect
Time: 282
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
3 rows in set (0.03 sec)
至此，一个master-slave的配置已经完成。
change master to master_host='172.168.2.148',master_user='repluser',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=120
如果需要做级联复制，则加一下
log-slave-updates=1
加完这个重启从机，查看是否生效:
mysql> show variables like 'log_slave_%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| log_slave_updates | ON |
+-------------------+-------+
1 row in set (0.00 sec)
配好了从机有可能需要重启一下
如果多个DB复制
binlog-do-db=DB1
binlog-do-db=DB2
binlog-do-db=DB3
replicate-do-db=DB1
replicate-do-db=DB2
replicate-do-db=DB3
分享到：
发表评论
相关推荐
Master-Slave的数据库机构解决了很多问题，特别是read/write比较高的web2.0应用： 1、写操作全部在Master结点执行，并由Slave数据库结点定时(默认60s)读取Master的bin-log 2、将众多的用户读请求分散到更多的...
MySQL的Master-Slave架构是一种常见的数据库高可用性和负载均衡解决方案。它允许数据从一个主服务器（Master）实时同步到一个或多个从服务器（Slave）。在这种架构中，所有的写操作都在主服务器上执行，而从服务器则...
里面实现的是动态切换数据源，大家都是知道，spring2之后添加AbstractRoutingDataSource这个东西，这个就可以实现切换数据源，实现思路是：先按照搭建MySQL的MasterSlave架构.doc>>(我上传有资源)，搭建好，然后动态...
下面我们将深入探讨MySQL主从复制的配置、工作原理和相关知识点。 一、主从复制配置 1. **环境准备**：首先，你需要在主服务器和从服务器上安装并配置MySQL。确保两个服务器之间网络连通，并且可以相互访问。 2. ...
至此，ActiveMQ的JDBC Master/Slave配置已经完成，此时，如果Master节点宕机，Slave节点会自动转换角色成为新的Master，保证消息代理服务的连续性。当原先的Master节点恢复后，会重新加入集群中，并回到Slave角色，...
MySQL的主从（Master-Slave）数据同步配置是一种常见的数据库高可用性和负载均衡解决方案，它允许数据在多个服务器之间复制，确保数据的一致性，并且可以在主服务器故障时提供故障转移。以下是对这一主题的详细说明...
- **Slave配置**： - `replicate-do-db`：指定需要从Master同步的数据库名。 - `log_bin`：同样开启二进制日志，但这是为了记录从Master接收的更改。 - `relay_log`：中继日志，存储从Master接收到的事件，供...
MySQL的主从复制（Master-Slave Replication）是一种常用的数据冗余和负载均衡策略，它允许数据库系统在多个服务器之间同步数据。当一个MySQL服务器（主服务器，Master）接收到写操作时，这些更改会被记录到二进制...
### MySQL 5.5 Master-Slave (Replication) 配置详解 #### 一、基础知识简介 在深入了解具体的配置步骤之前，我们首先简要回顾一下MySQL中的Master-Slave复制机制的基础概念。 - **主从复制（Master-Slave ...
MySQL 5.7 主从配置是指在两个或多个 MySQL 服务器之间实现数据的实时同步复制，主服务器作为数据的写入入口，slave 服务器作为数据的读取入口。这种配置可以提高数据库的高可用性和读取性能。 1. 主从配置前提 ...
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs This error generally occur when we clone the master to slaver. Delete auto.cnf of mysql, and then ...
MySQL 数据库主从配置 MySQL 数据库主从配置是指在 MySQL 数据库中实现主从复制的配置过程。该配置过程主要涉及到主服务器和从服务器的配置，旨在实现数据库的实时同步。 首先，需要在主服务器上打开二进制日志，...
要配置 JDBC Master Slave 模式，我们需要在每个 ActiveMQ 实例的配置文件（activemq.xml）中添加数据源，这些数据源都指向同一个数据库。例如： ```xml ${activemq.base}/data" dataSource="#mysql-ds" ...
mysql slave切换为master 测试
### MySQL 主从库配置详解 #### 一、MySQL 安装与环境变量设置 在进行 MySQL 主从库配置之前，我们需要确保已经正确安装了 MySQL，并设置了相应的环境变量。以下是具体的步骤： 1. **下载 MySQL 免安装版**： - ...