MySQL数据库主从同步

来源：互联网发布：最大淘宝小号交易平台编辑：程序博客网时间：2024/05/19 12:16

一. 环境搭建

主master：192.168.76.133
从slave： 192.168.76.134

建立测试用数据库及数据表

mysql> create table employees(-> employee_id INT NOT NULL AUTO_INCREMENT,-> name char(20) NOT NULL, -> e_mail varchar(50),-> PRIMARY KEY(employee_id));mysql> desc employees;+-------------+-------------+------+-----+---------+----------------+| Field       | Type        | Null | Key | Default | Extra          |+-------------+-------------+------+-----+---------+----------------+| employee_id | int(11)     | NO   | PRI | NULL    | auto_increment || name        | char(20)    | NO   |     | NULL    |                || e_mail      | varchar(50) | YES  |     | NULL    |                |+-------------+-------------+------+-----+---------+----------------+

插入数据

mysql> insert into employees values    -> (1,'TOM','tom@example.com'),    -> (2,'Jerry','jerry@example.com');mysql> select * from employees;+-------------+-------+-------------------+| employee_id | name  | e_mail            |+-------------+-------+-------------------+|           1 | TOM   | tom@example.com   ||           2 | Jerry | jerry@example.com |+-------------+-------+-------------------+

二. 主库服务器配置

1. 设置服务器编号，开启二进制日志

编辑/etc/my.cnf文件

[mysqld]server-id       = 1             #设置服务器编号log-bin         = /usr/local/mysql/data/mysql-bin/binlog#启用二进制日志 设定路径名 此目录mysql有权写入. #该路径指定了存放二进制日志的目录为mysql-bin  日志文件名前缀为binlog-------可选------------------------------#binlog-do-db = 数据库名   指定要同步的数据库#binlog-ignore-db=数据库名 指定不同的数据库

重启mysqld服务使配置生效

service mysqld restart

查看是否生效

cd /usr/local/mysql/data/mysql-bin/[root@wzz mysql-bin]# lltotal 8-rw-rw----. 1 mysql mysql 106 Feb 16 12:23 binlog.000001-rw-rw----. 1 mysql mysql  46 Feb 16 12:23 binlog.index

2. 创建从库复制账号

mysql> grant replication slave on *.* to 'rep'@'192.168.76.134' identified by 'slave';Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)

3. 只读锁定拒绝写操作

mysql> flush tables with read lock;

4.获取当前日志文件和记录点

mysql> show master status;+---------------+----------+--------------+------------------+| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |+---------------+----------+--------------+------------------+| binlog.000001 |      334 |              |                  |+---------------+----------+--------------+------------------+

5. 新开XShell窗口，dump数据库

dump数据

mysqldump -uroot -poldboy -A -B --events --master-data=2 >/opt/rep.sql

备份拷贝至从库服务器

scp /opt/rep.sql root@192.168.76.134:/home/wzz/ #拷贝至从库

主库解锁

mysql> unlock tables;Query OK, 0 rows affected (0.00 sec)

备份灌到从库

mysql -uroot -poldboy < /home/wzz/rep.sql # 在从库执行此操作

三. 从库服务器配置

1. 设置从库ID

主从库ID必须唯一修改my.cnf 后重启mysql生效

server-id       = 2#log-bin=mysql-bin  #从库不级联，可以不开启log-bin

重启mysql使配置生效

2. CHANGE MASTER TO设置连接信息

通过CHANGE MASTER TO 指定从库连接主库时所必须的信息

mysql> CHANGE MASTER TO  MASTER_HOST='192.168.76.133', MASTER_PORT=3306,               # 注意数字不能有引号MASTER_USER='rep', MASTER_PASSWORD='slave', MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=334;             # 注意数字不能有引号

这些信息会存放在从库data目录下的master.info里。（我的data目录位于/usr/local/mysql)

[root@wzz ~]# cat /usr/local/mysql/data/master.info 18binlog.000001334192.168.76.133repslave3306600

3. 从库开始同步

开启从库同步

mysql> start slave

检查从库状态

mysql> show slave status\GSlave_IO_State: Waiting for master to send event                  Master_Host: 192.168.76.133                  Master_User: rep                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: binlog.000001          Read_Master_Log_Pos: 334               Relay_Log_File: nagios-client03-relay-bin.000002                Relay_Log_Pos: 249        Relay_Master_Log_File: binlog.000001             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: 334              Relay_Log_Space: 415              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: 0Master_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

四. 测试

主库的增删改操作会自动同步到从库上
relay-log.info
master.info
show processlist\G
show master status\G
show slave status\G

0 0