一、原理:
1.主服务器建立二进制日志,每产生语句或磁盘变化,写进日志
2.从服务器监听主服务器二进制日志,读取二进制日志建立relaylog
3.要从主服务器读取二进制日志,那么需要主服务器授权复制账号。从而从服务器利用复制账号来监听主服务器日志
一句话: 主服务建立二进制日志,从服务器建立relay-log,主服务器建立复制账号供从服务器来连接监听用。
二、建立主从集群配置 一主多从
master(主192.168.1.110) slave1(从192.168.1.111)、slave2(从192.168.1.112)
(1) 主服务建立二进制日志,从服务器建立relay-log
a.配置my.cnf文件
master配置:/etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#skip-grant-tables
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#建立主服务器日志和监听服务器日志格式
#server-id
server-id=110
log-bin=mysql-bin #二进制日志(/var/lib/mysql/)
#使用语句(statement)级别的日志格式还是磁盘行(row)级别的日志格式 还要混合(mixed)方式
binlog-format=mixed #这里通过混合的方式来监听日志格式
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
slave1配置:/etc/my.cof
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#skip-grant-tables
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#server-id
server-id=111
relay-log=mysql-relay
slave2配置:/etc/my.cof
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#skip-grant-tables
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#server-id
server-id=112
#relay log
relay-log=mysql-relay
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
b. 重启mysqld 加载配置
master:service mysqld restart
slave1:service mysqld restart
slave2:service mysqld restart
(2)建立授权复制账号,从服务器利用复制账号来监听主服务器二进制日志
a.建立授权复制账号
mysql> grant replication client, replication slave on *.* to 'root@%' identified by '123456'; //复制账号为root,密码为123456
mysql> flush privileges;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 341 | | |
+------------------+----------+--------------+------------------+
b.在slave1,slave2中,需要建立master复制信息:
master_host="192.168.1.110"
master_user='root'
master_password='123456'
master_log_file='mysql-bin.000001' //当前的二进制日志文件名字
master_log_pos=341 //二进制日志写入的位置
mysql> change master to master_host="192.168.1.110",master_user='root',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=341;
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.110
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 341
Relay_Log_File: mysql-relay.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
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: 341
Relay_Log_Space: 106
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:
1 row in set (0.01 sec)
ERROR:
No query specified
//启动各个节点的slave
mysql> start slave;
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
//可见该节点正在等待master的事件
常用命令:
show master status; //查看master的状态,当前的日志以及位置
show slave status; //查看slave的状态
reset slave; //重置slave的状态
start slave; //启动slave状态(开始监听master的变化)
stop slave;
1.主服务器建立二进制日志,每产生语句或磁盘变化,写进日志
2.从服务器监听主服务器二进制日志,读取二进制日志建立relaylog
3.要从主服务器读取二进制日志,那么需要主服务器授权复制账号。从而从服务器利用复制账号来监听主服务器日志
一句话: 主服务建立二进制日志,从服务器建立relay-log,主服务器建立复制账号供从服务器来连接监听用。
二、建立主从集群配置 一主多从
master(主192.168.1.110) slave1(从192.168.1.111)、slave2(从192.168.1.112)
(1) 主服务建立二进制日志,从服务器建立relay-log
a.配置my.cnf文件
master配置:/etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#skip-grant-tables
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#建立主服务器日志和监听服务器日志格式
#server-id
server-id=110
log-bin=mysql-bin #二进制日志(/var/lib/mysql/)
#使用语句(statement)级别的日志格式还是磁盘行(row)级别的日志格式 还要混合(mixed)方式
binlog-format=mixed #这里通过混合的方式来监听日志格式
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
slave1配置:/etc/my.cof
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#skip-grant-tables
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#server-id
server-id=111
relay-log=mysql-relay
slave2配置:/etc/my.cof
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#skip-grant-tables
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#server-id
server-id=112
#relay log
relay-log=mysql-relay
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
b. 重启mysqld 加载配置
master:service mysqld restart
slave1:service mysqld restart
slave2:service mysqld restart
(2)建立授权复制账号,从服务器利用复制账号来监听主服务器二进制日志
a.建立授权复制账号
mysql> grant replication client, replication slave on *.* to 'root@%' identified by '123456'; //复制账号为root,密码为123456
mysql> flush privileges;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 341 | | |
+------------------+----------+--------------+------------------+
b.在slave1,slave2中,需要建立master复制信息:
master_host="192.168.1.110"
master_user='root'
master_password='123456'
master_log_file='mysql-bin.000001' //当前的二进制日志文件名字
master_log_pos=341 //二进制日志写入的位置
mysql> change master to master_host="192.168.1.110",master_user='root',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=341;
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.110
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 341
Relay_Log_File: mysql-relay.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
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: 341
Relay_Log_Space: 106
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:
1 row in set (0.01 sec)
ERROR:
No query specified
//启动各个节点的slave
mysql> start slave;
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
//可见该节点正在等待master的事件
常用命令:
show master status; //查看master的状态,当前的日志以及位置
show slave status; //查看slave的状态
reset slave; //重置slave的状态
start slave; //启动slave状态(开始监听master的变化)
stop slave;