Chinaunix首页 | 论坛 | 博客
  • 博客访问: 104308
  • 博文数量: 22
  • 博客积分: 45
  • 博客等级: 民兵
  • 技术积分: 225
  • 用 户 组: 普通用户
  • 注册时间: 2012-05-27 17:53
文章分类
文章存档

2017年(1)

2016年(8)

2015年(11)

2014年(1)

2013年(1)

我的朋友

分类: Mysql/postgreSQL

2015-10-15 11:11:54

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;

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

阅读(1457) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~