03 mysql主从复制架构及实现


实战:主从复制

配置 

master:192.168.1.131 CentOS7.2

slave: 192.168.1.132 CentOS7.2


#主结点

[root@master ~]# yum -y install mariadb-server

[root@master ~]# vim /etc/my.cnf

添加

log-bin=master-bin

server-id=1

innodb_file_per_table=ON

skip_name_resolve=ON

[root@master ~]# ls /etc/my.cnf.d/

client.cnf  mysql-clients.cnf  server.cnf

[root@master ~]# systemctl start mariadb.service

[root@master ~]# mysql

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'log_bin'; 

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin       | ON    |

+---------------+-------+

MariaDB [(none)]> SHOW MASTER LOGS;

+-------------------+-----------+

| Log_name          | File_size |

+-------------------+-----------+

| master-bin.000001 |     30331 |

| master-bin.000002 |   1038814 |

| master-bin.000003 |       245 |

+-------------------+-----------+

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'server_id';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id     | 1     |

+---------------+-------+

MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.%.%' IDENTIFIED BY 'replpass';

MariaDB [(none)]> FLUSH PRIVILEGES;


#从结点

[root@slave ~]# yum -y install mariadb-server

[root@slave ~]# vim /etc/my.cnf

添加

relay-log=relay-log

relay-log-index=relay-log.index

server-id=7

innodb_file_per_table=ON

skip_name_resolve=ON

[root@slave ~]# systemctl start mariadb.service

[root@slave ~]# mysql

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'relay_log';

+---------------+-----------+

| Variable_name | Value     |

+---------------+-----------+

| relay_log     | relay-log |

+---------------+-----------+

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'server_id';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id     | 7     |

+---------------+-------+

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.1.131',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000003',MASTER_LOG_POS=245;

MariaDB [(none)]> START SLAVE;


#主结点 

MariaDB [(none)]> CREATE DATABASE mydb;

MariaDB [(none)]> SHOW DATABASES;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mydb               |

| mysql              |

| performance_schema |

| test               |

+--------------------+

MariaDB [(none)]> SHOW MASTER STATUS;

+-------------------+----------+--------------+------------------+

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+-------------------+----------+--------------+------------------+

| master-bin.000003 |      580 |              |                  |

+-------------------+----------+--------------+------------------+


#从结点:

MariaDB [(none)]> show slave status  \G

*************************** 1. row ***************************

  Slave_IO_State: Waiting for master to send event

 Master_Host: 192.168.1.131

 Master_User: repluser

 Master_Port: 3306

Connect_Retry: 60

 Master_Log_File: master-bin.000003

 Read_Master_Log_Pos: 580

  Relay_Log_File: relay-log.000002

Relay_Log_Pos: 865

Relay_Master_Log_File: master-bin.000003

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: 580

 Relay_Log_Space: 1153

 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)


结果:测试成功