mysql数据库主从配置

mysql数据库主从配置：

1. 是要先在配置文件里启用mysql_bin日志

server-id               = 064071
log-bin                 = mysql-bin

server-id是唯一标识mysql的标识 ，为主从使用。

log-bin是指明启用mysql-bin日志，二进制形式。

2. 在主服务器

MariaDB [(none)]> show variables like '%bin%';
+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| innodb_api_enable_binlog                | OFF                  |
| innodb_locks_unsafe_for_binlog          | OFF                  |
| log_bin                                 | ON                   |
| sync_binlog                             | 1                    |
+-----------------------------------------+----------------------+
20 rows in set (0.00 sec)

以上命令可以查看log_bin动作是否启用。

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

可以查看主服务器用的mysql-bin日志的名字以及位置。

MariaDB [(none)]> create user replication@172.20.64.65 identified by 'replication';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> grant replication slave on replication@172.20.64.65;
ERROR 1046 (3D000): No database selected
MariaDB [(none)]> grant replication slave on *.* replication@172.20.64.65;
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 'replication@172.20.64.65' at line 1
MariaDB [(none)]> grant replication slave on *.* to replication@172.20.64.65;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> grant replication slave on *.* to replication@172.20.64.65 identified by 'replication';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privaliges;
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 'privaliges' at line 1
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

如上为启用主服务器的拷贝 动作。

-----------------------------------

从服务器需要如下配置：

change master to master_host='172.20.64.70',master_port=3358,master_user='replication',master_password='replication',master_log_file='mysql-bin.000011',master_log_pos=365;
start slave ;
------------------------------------
change master to master_host='172.20.64.64',master_port=3358,master_user='replication',master_password='replication',master_log_file='mysql-bin.000003',master_log_pos=771;
start slave ;

可以使用如下查看配置是否正常

show slave status \g;

如上配置基本问题就没有了