CNDBA社区
1 关闭主从服务器防火墙
[root@www.cndba.cn ~]# systemctl stop firewalld
[root@www.cndba.cn ~]# systemctl disable firewalld
2 设置主数据库的配置文件：
在/etc/my.cnf文件中，加上
server-id=1 #数据库的唯一ID号，默认以1开始
log-bin= /data/mysql/binlogs/master-bin #启用二进制日志
3 在主数据库建立账户并授权从数据库访问：
grant replication slave on *.* to 'mysql'@'%' identified by 'mysql';
4 重新启动MariaDB数据库
MariaDB [(none)]> Ctrl-C -- exit!
Aborted
[root@www.cndba.cn mysql]# systemctl restart mysql
5 检查主数据库的master状态:
[root@www.cndba.cn mysql]# mysql -uroot -proot
Welcome to the MariaDB monitor. Commands end with ; or /g.
Your MariaDB connection id is 10
Server version: 10.2.10-MariaDB-log MariaDB Server
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 342 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
6 设置从数据库的配置文件：
在/etc/my.cnf文件中，加上
#log-bin = /mydata/mysql-bin //加上注释
relay-log = /data/mysql/relaylogs/relay-bin //如果不设置，默认是按主机名 + "-relay-bin",例如：mariadb2-relay-bin.000001 生成relay log。
server-id=68
7 重启从数据库
[root@mariadb2 support-files]# service mysql restart
Restarting mysql (via systemctl): [ OK ]
8 使用从数据库命令行方式配置：
打开命令行，并登录到从数据库的MySQL.
change master to master_host='192.168.1.67',master_user='mysql',master_password='mysql',master_log_file='mysql-bin.000003',master_log_pos=342;
请注意以下几点：
1)master_host是指主服务器的IP
2)master_user是指使用哪个用户登录主服务器
3)master_password是指登录密码
4)master_log_file是指在第4个步骤中的File名称
5)master_log_pos是指在第4个步骤中的Position
9 启用从数据库复制功能(这个步骤是在从数据库的MySQL中配置)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
10 查看从数据库的复制功能状态
注意：结果中Slave_IO_Running和Slave_SQL_Running必须为Yes，如果不是，需要根据提示的错误修改。
MariaDB [(none)]> show slave status/G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.67
Master_User: mysql
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 342
Relay_Log_File: mariadb2-relay-bin.000002
Relay_Log_Pos: 555
Relay_Master_Log_File: mysql-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: 342
Relay_Log_Space: 867
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: 67
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
1 row in set (0.00 sec)
ERROR: No query specified
11 测试数据库同步
—主库创建数据库
MariaDB [(none)]> create database cndba default character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show database;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'database' at line 1
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| cndba |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
--备库查询
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| cndba |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.28 sec)
--主库创建表
MariaDB [(none)]> use cndba
Database changed
MariaDB [cndba]> create table test(name char(10));
Query OK, 0 rows affected (0.01 sec)
MariaDB [cndba]> insert into test values('cndba');
Query OK, 1 row affected (0.01 sec)
MariaDB [cndba]> commit;
Query OK, 0 rows affected (0.00 sec)
MariaDB [cndba]> select * from test;
+-------+
| name |
+-------+
| cndba |
+-------+
1 row in set (0.00 sec)
--备库查询
MariaDB [(none)]> use cndba
Database changed
MariaDB [cndba]> select * from test;
+-------+
| name |
+-------+
| cndba |
+-------+
1 row in set (0.00 sec)
版权声明：本文为博主原创文章，未经博主允许不得转载。
