mysql 主从数据库搭建_MySQL（mariadb）主从数据库搭建详解

配置主服务器

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

[mysqld]

server_id=1 #为当前节点设置一个全局惟一的ID号

datadir=/mysql/data

log_bin=/mysql/logbin/master-bin #主服务器必须开启二进制日志

innodb_file_per_table

socket=/var/lib/mysql/mysql.sock

...

[root@localhost ~]# systemctl restart mariadb.service #重新启动服务使配置生效

[root@localhost ~]# mysql

MariaDB [(none)]> show master status\G; #查看主服务器的为二进制文件信息，需要在级联服务器上启动复制线程

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

File: master-bin.000006

Position: 8666

Binlog_Do_DB:

Binlog_Ignore_DB:

MariaDB [(none)]> grant replication slave on *.* to repluser@'172.18.153.%' identified by 'centos'; #创建有复制权限的用户账号 ，规定172.18.153/24内的IP可以连接主服务器

#这个地方特别注意先查看二进制文件，然后创建用户，否则级联服务器上将二进制文件同步过去以后将找不到用户

配置级联服务器

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

[mysqld]

server_id=2 #id号唯一，无先后顺序

datadir=/mysql/data

innodb_file_per_table

log_bin=/mysql/logbin/cascade-bin #开启级联服务器的二进制日志

log_slave_updates #开启与主服务器的日志同步，启用级联复制

socket=/var/lib/mysql/mysql.sock

...

[root@localhost ~]# systemctl restart mariadb.service

[root@localhost ~]# mysql

MariaDB [(none)]> show master status\G; #获取级联服务器的二进制信息，在从服务器上启动复制线程

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

File: cascade-bin.000003

Position: 9066

Binlog_Do_DB:

Binlog_Ignore_DB:

MariaDB [(none)]> help change master to; #启动的复制线程的语法，可以帮助help获取

...

CHANGE MASTER TO

MASTER_HOST='master2.mycompany.com', #主服务器的host

MASTER_USER='replication', #使用有复制权限的用户

MASTER_PASSWORD='bigs3cret', #复制权限的用户的密码

MASTER_PORT=3306, #数据库端口

MASTER_LOG_FILE='master2-bin.001', #主服务器上的二进制文件名称

MASTER_LOG_POS=4, #获取二进制文件的记录的起始位置(比如这个日志，现在创建一个数据库和一个表，这个创建表和库的记录就会在这个日志的4开始记录，也就是说在这个日志里从4以后存储的记录是我刚才创建了一个数据库和表)

MASTER_CONNECT_RETRY=10; #连接的时间

...

MariaDB [(none)]> CHANGE MASTER TO #开启线程

-> MASTER_HOST='172.18.153.7',

-> MASTER_USER='repluser',

-> MASTER_PASSWORD='centos',

-> MASTER_PORT=3306,

-> MASTER_LOG_FILE='master-bin.000006',

-> MASTER_LOG_POS=8666,

-> MASTER_CONNECT_RETRY=10;

MariaDB [(none)]> start slave; #开启

MariaDB [(none)]> show slave status\G; #查看当前的信息

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

Slave_IO_State: Waiting for master to send event

Master_Host: 172.18.153.7 #主服务器的host

Master_User: repluser #主服务器上创建的有复制权限的用户

Master_Port: 3306

Connect_Retry: 10

Master_Log_File: master-bin.000006 #二进制文件名

Read_Master_Log_Pos: 8666 #记录点

Relay_Log_File: mariadb-relay-bin.000002

Relay_Log_Pos: 1376

Relay_Master_Log_File: master-bin.000006

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

Relay_Log_Space: 1672

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 #连接了一台主服务器

配置从服务器

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

[mysqld]

server_id=3

datadir=/mysql/data

read_only #开启只读，从服务器的配置都是由级联服务器复制来的，不需要写操作，从服务器用来数据库的读操作,实现数据库的读写分离

socket=/var/lib/mysql/mysql.sock

...

[root@localhost ~]# systemctl restart mariadb.service

[root@localhost ~]# mysql

MariaDB [(none)]> CHANGE MASTER TO

-> MASTER_HOST='172.18.153.17',

-> MASTER_USER='repluser',

-> MASTER_PASSWORD='centos',

-> MASTER_PORT=3306,

-> MASTER_LOG_FILE='cascade-bin.000003',

-> MASTER_LOG_POS=9066,

-> MASTER_CONNECT_RETRY=10;

MariaDB [(none)]> start slave;

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

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

Slave_IO_State: Waiting for master to send event

Master_Host: 172.18.153.17

Master_User: repluser

Master_Port: 3306

Connect_Retry: 10

Master_Log_File: cascade-bin.000003

Read_Master_Log_Pos: 9066

Relay_Log_File: mariadb-relay-bin.000002

Relay_Log_Pos: 531

Relay_Master_Log_File: cascade-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: 9066

Relay_Log_Space: 827

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

测试,在主服务器上创建一个库，在级联和从服务器刷新即可看见新建的数据库。

MariaDB [(none)]> create database test1;

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| test |

| test1 |

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

错误解决

在开启了复制线程之后会遇到几处错误

Last_IO_Error: error connecting to master 'user@172.18.153.17:3306' - retry-time: 10 retries: 86400 message: Host '172.18.153.27' is not allowed to connect to this MariaDB server

这个错误是因为你的用户权限问题，假如先获取了二进制日志，然后进行创建的用户，这个时候在从服务器会报这个错误，因为从服务器无法根据二进制文件进行数据同步，只要重新配置即可

Last_IO_Error: error connecting to master 'user@172.18.153.7:3306' - retry-time: 10 retries: 86400 message: Can't connect to MySQL server on '172.18.153.7' (113)

这个错误是因为你的服务器的防火墙和selinux的问题，还有可能是你的网络问题(检查你服务器之间的IP是否可以ping通)

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

这个错误是因为你的日志文件的问题，主服务器无法读取你的二进制日志。这个问题先关闭服务，删除日志文件，重启服务让他自动生成即可。