MySQL主从
准备
- 安装好mysql
- 关闭防火墙和沙盒
- 主数据库ID:192.168.2.55
- 从数据库ID:192.168.2.88
主数据库配置
1.编辑配置文件
vim /etc/my.cnf
# binary logging is required for replication
log-bin=master-bin
log-slave-updates=true
# binary logging format - mixed recommended
binlog_format=mixed
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1
2.重启mysql服务
service mysqld restart
3.进入mysql
mysql -uroot -p
未设置密码
4.给备份用户授权,允许slave从从服务器登录到主服务器
自定义备份用户名(slave)和密码(123.com):
mysql> grant replication slave on *.* to 'slave'@'192.168.2.%' identified by '123.com';
Query OK, 0 rows affected (0.00 sec)
5.刷新系统授权并查看master状态
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 486 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
从数据库配置
1.编辑配置文件
vim /etc/my.cnf
# binary logging is required for replication
log-bin=mysql-bin
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
# binary logging format - mixed recommended
binlog_format=mixed
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 2
2.重启mysql服务
service mysqld restart
3.进入mysql
mysql -uroot -p123456
已设置的密码:123456
4.获取授权
若已经授权,可重置slave参数,重新授权。
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_host='192.168.2.55',master_user='slave',master_password='123.com',master_log_file='mysql-bin.000004',master_log_pos=486;
Query OK, 0 rows affected (0.02 sec)
5.开启从数据库并查看从服务器状态
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.55
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 486
Relay_Log_File: relay-log-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000004
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: 486
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
成功!