MySQL主从复制实战

MySQL主从复制实战

主mysql服务器负责写，从mysql服务器负责读，既有效率又实现数据冗余备份，若主服务器down掉，从服务器还可以使用。

Master

系统环境准备

Master：192.168.140.128
Slave：192.168.140.129
yum –y install mariadb mariadb-server
systemctl start mariadb
systemctl enable mariadb
mysql_secure_installation

配置文件/etc/my.cnf

[root@master ~]# grep -v ‘#’ /etc/my.cnf
[client]
port = 3306
socket=/var/lib/mysql/mysql.sock
[mysqld]
port = 3306
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1MB
table_open_cache = 256
sort_buffer_size = 1MB
read_buffer_size = 1MB
read_rnd_buffer_size = 4MB
myisam_sort_buffer_size = 64MB
thread_cache_size = 8
query_cache_size = 16MB
thread_concurrency = 8
log-bin = mysql-bin
binlog_format = mixed
server-id = 1
[mysqldump]
quick
max_allowed_packer = 16MB
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 128MB
sort_buffer_size = 128MB
read_buffer = 2MB
write_buffer = 2MB
[mysqlhotcopy]
interactive-timeout

数据库配置

数据库服务器命令创建用户及密码并设置权限。

MariaDB[(none)]>grant replication slave on . to ‘test’@’%'identified by ‘test’;
Query OK, 0 rows affected (0.00 sec)

查看bin-log文件及position点。

MariaDB [(none)]> show master status;
±-----------------±---------±-------------±-----------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
±-----------------±---------±-------------±-----------------+
| mysql-bin.000005 | 1610 | | |
±-----------------±---------±-------------±-----------------+
1 row in set (0.00 sec)

创建数据库

MariaDB[(none)]> create database mysql_ab_test charset=utf8;
Query OK, 1 row affected (0.00 sec)

MariaDB[(none)]> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| mysql_ab_test |
| performance_schema |
±-------------------+
4 rows in set (0.01 sec)

MariaDB[(none)]> use mysql_ab_test;
Database changed

创建表

MariaDB[mysql_ab_test]> create table t0 (id varchar(20),name varchar(20));
Query OK, 0 rows affected (0.00 sec)

iaDB[mysql_ab_test]> show tables;
±------------------------+
| Tables_in_mysql_ab_test |
±------------------------+
| t0 |
±------------------------+
1 row in set (0.00 sec)

插入数据

MariaDB[mysql_ab_test]> insert into t0 values (‘001’,‘zhangsan’);
Query OK, 1 row affected (0.01 sec)

MariaDB[mysql_ab_test]> insert into t0 values (‘002’,‘lisi’);
Query OK, 1 row affected (0.00 sec)

Slave

配置文件/etc/my.cnf

[root@slave ~]# grep -v ‘#’ /etc/my.cnf
[client]
port = 3306
socket=/var/lib/mysql/mysql.sock
[mysqld]
port = 3306
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1MB
table_open_cache = 256
sort_buffer_size = 1MB
read_buffer_size = 1MB
read_rnd_buffer_size = 4MB
myisam_sort_buffer_size = 64MB
thread_cache_size = 8
query_cache_size = 16MB
thread_concurrency = 8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id = 2
[mysqldump]
quick
max_allowed_packer = 16MB
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 128MB
sort_buffer_size = 128MB
read_buffer = 2MB
write_buffer = 2MB
[mysqlhotcopy]
interactive-timeout

数据库配置

Slave指定master IP、用户名、密码、Bin-log文件名及position。

MariaDB[(none)]>change master to
master_host=‘192.168.140.128’,master_user=‘test’,master_password=‘test’,master_log_file=‘mysql-bin.000005’,master_log_pos=1610;
Query OK, 0 rows affected (0.04 sec)

在slave中启动slave start

MariaDB [(none)]> slave start;
Query OK, 0 rows affected (0.00 sec)

执行show slave status\G 查看mysql主从状态。

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.140.128
Master_User: test
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 1610
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000005
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: 1610
Relay_Log_Space: 825
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)

查看是否复制成功

查看是否存在数据库，表，数据

MariaDB [(none)]> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| mysql_ab_test |
| performance_schema |
±-------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> use mysql_ab_test;
Database changed

MariaDB [mysql_ab_test]> show tables;
±------------------------+
| Tables_in_mysql_ab_test |
±------------------------+
| t0 |
±------------------------+
1 row in set (0.00 sec)

MariaDB [mysql_ab_test]> select * from t0;
±-----±---------+
| id | name |
±-----±---------+
| 001 | zhangsan |
| 002 | lisi |
±-----±---------+
2 rows in set (0.00 sec)