Mysql 主从配置

这篇博客会详细介绍如何配置主从配置,但重点是想分享如何当配置未成功时,如何调试。

原文连接(推荐这个看 比较清晰)

https://www.liutao1995.top/mysql-zhu-cong-pei-zhi-shang/

>相信在很多其他地方也已经有关于如何配置MySQL的相关知识介绍,这里,我也会把自己配置成功的过程介绍一下:

一 作用

MySQL主从配置其实是一个MySQL增量热备份技术。要知道,MySQL的查询压力一般大于写入压力,那可以用数据库主从配置做数据分离或者缓存。可以用于查询优化。

 二 配置过程

> 这里我用的是ubuntu14.04版本,mysql的版本是5.7。

 2.1 主从同步前提

1)在做主从同步前必须保证mysql版本兼容(版本相同或者从库版本大于主库)

2)保证同步前数据库文件必须一致

注释:从库版本大于主库版本,这是因为数据库在升级时,有向下兼容,所以一般高版本的数据可以兼容低版本数据库,反之则不然。但是,这里在做主从同步时,最好保证是版本相同,因为并不一定高版本就完全兼容低版本数据库,可以自行去数据库官网查询。但是,如果做数据迁移时,很有可能就会一定要求从库的版本高于主库,那么需要事先测试。

2.2  开始同步----主数据库操作

这里为了保证主从数据库环境一致,所以我采用vagrant搭建的环境:ubuntu14.04,mysql5.7。

`vagrant init`初始化两个虚拟机,ip地址分别为:

`192.168.10.2`和`192.168.10.3`,前者是主数据库所在服务器,后者是从数据库所在服务器。

1.修改主数据库的配置文件:(我将主数据库服务器名为S2)

找到mysql的配置文件:

```

root@S2:cd /etc/mysql/mysql.conf.d/

root@S2:/etc/mysql/mysql.conf.d# vi mysqld.cnf

```

这里具体的mysql.cnf文件,版本不同,文件名会有不同。找到正确的mysql配置修改即可。

2 添加配置(如果有被注释的,直接去掉注释开启即可)

[mysqld]下的选项为服务器端的配置内容

```

[mysqld]

pid-file        = /var/run/mysqld/mysqld.pid

socket          = /var/run/mysqld/mysqld.sock

datadir        = /var/lib/mysql

log-error      = /var/log/mysql/error.log

# By default we only accept connections from localhost

#将标准IP地址127.0.0.1 替换为服务器的IP地址

bind-address    = 192.168.10.2

server-id = 1000

log_bin = /var/log/mysql/mysql-bin.log

#表示只备份testdb

binlog_do_db = testdb

```

解释:

`pid-file`:存放进程的pid

`socket`:socket连接

`datadir`:数据库的目录文件

`log-error`:错误日志的路径

`bind-address`:这台服务器绑定的ip地址(之前已经说了,主数据库服务器的ip地址是192.168.10.2)

`server-id`:选项标识,当前数据库在集群中的id,范围从1-2321,主库从库都必须设置id,且id不同

`log_bin`:所有在主数据库中的变更操作都会被记录在该文件里,然后从库会将主库里的所有变更操作复制,再在从库里执行以此达到和主库的数据一致

`binlog_do_db `:指定将从主服务器上复制的数据库。您可以通过为所需的所有数据库重复此行来包含多个数据库。当然还有很多其他配置情况,具体自行查看

3.退出保存 重启mysql

完成所有更改后,继续保存并退出配置文件,刷新MySQL。

`sudo service mysql restart`

4.为从库配置账号和赋予账号权限

打开shell,进入mysql中:

`mysql -u root -p`

```

...

//create user '用户名'@'从库ip地址' identified by '用户密码';  创建用户及密码

> create user 'slave_1'@'192.168.10.3' identified by 'slave_1';

//grant replication slave on *.* to '上面配置的用户名'@'上面配置的从库ip地址' identified by '上面配置的密码';  赋予权限

>grant replication slave on *.* to 'slave_1'@'192.168.10.3' identified by 'slave_1';

```

 5.刷新MySQL的系统权限相关表,否则会出现拒绝访问

```

mysql>  FLUSH PRIVILEGES;

```

 6.锁定表,将主库的testdb数据库导出.sql文件到从库中,解锁

下面提供一种思路:

进入主库的mysql中:

```

mysql> use testdb;  //进入testdb库

mysql> FLUSH TABLES WITH READ LOCK;//锁定数据库以防止任何新的更改:

```

退出mysql命令行,导出想要导出的数据库并生成.sql文件:

```

系统命令行

mysqldump -uusername -ppassword --databases testdb>testdb.sql

```

就可以看到当前目录下有一个testdb.sql文件,之后将这个文件通过scp的方式传送到从数据库所在的服务器(例如:scp path/newdatabase.sql admin@128.xxx:/tmp)

导出数据后,重新进入mysql命令行,完成解锁

```

> use testdb;

>UNLOCK TABLES;

```

这里需要先完成锁表再导处数据,一是为了保证数据的完整性,不然很有可能部分数据丢失,二是为了防止被恶意攻击。

好了,以上就是主数据库的主要操作了,接下来看从数据库如何配置。

2.3  开始同步----从数据库操作

> 从数据库的配置也差不多,需要修改从服务器上的数据库配置文件,然后将从主库上的数据库导入从库。

我们先把数据库导入从库:

1. 进入mysql命令行

```

mysql> CREATE DATABASE testdb;//创建数据库

```

退出命令行模式,系统下运行

```

//将之前通过scp传过来的.sql文件添加到新创建的数据库中

mysql -u uusername -ppassword testdb CHANGE MASTER TO MASTER_HOST = '192.168.10.2',MASTER_USER ='slave_1',MASTER_PASSWORD ='slave_1',MASTER_LOG_FILE ='mysql-bin.000001',MASTER_LOG_POS = 107;

```

解释:

`MASTER_HOST `:主库ip地址

`MASTER_USER`:当初主库给从库设置的访问账号

`MASTER_PASSWORD `:访问账号密码

`MASTER_LOG_FILE`:主库的log_file(需要查看主库的master状态)

`MASTER_LOG_POS`:主库的log_pos(需要查看主库的master状态)

此处的`MASTER_LOG_FILE`和`MASTER_LOG_POS`是主库的master状态,所以我们需要到主库去查看。

* 到主库去,进入mysql:

mysql> SHOW MASTER STATUS;//查看主数据库的状态,之后可以看到下面这个表

```

mysql> SHOW MASTER STATUS;

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

| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000001 |      107 | newdatabase  |                  |

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

1 row in set (0.00 sec)

```

可以看到,File的值就是我们需要在从库设置的MASTER_LOG_FILE,Position 的值就是我们需要在从库设置的MASTER_LOG_POS

好了,在完成上述change命令后,它就将当前服务器指定为主服务器的从属。通过用户名和密码为从服务器数据库提供正确的登录主服务器数据库的凭据 最后,它让从服务器知道从日志文件的哪里开始复制; 主日志文件和日志位置来自我们以前记录的数字。 有了这个,你已经配置了一个主服务器和从服务器。

接下来可以去主数据库中任何为一个数据表添加记录,然后查看从数据库里的数据表是否会产生相应的记录。

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

上面是介绍如何配置主从数据库的教程。但是大多数人相信第一次都不一定会成功,那么如何解决呢?下面是排错思路:

之前在进入数据库的配置文件中,相信看到了`log-error      = /var/log/mysql/error.log`这么一个选项,所以,当发现经过上面的配置,从库没有产生正确的响应。就去`/var/log/mysql/error.log`文件查看,下面是我在配置中遇到过的几个问题,例如:

```

> cat /var/log/mysql/error.log

```

1130错误码:

```

2017-09-21T08:36:51.637972Z 9 [ERROR] Slave I/O for channel '': error connecting to master 'slave_1@192.168.10.2:3306' - retry-time: 60  retries: 32, Error_code: 1130

```

2003错误码:

```

2017-09-21T08:25:51.605374Z 9 [ERROR] Slave I/O for channel '': error connecting to master 'slave_1@192.168.10.2:3306' - retry-time: 60  retries: 21, Error_code: 2003

```

1593错误码

```

[ERROR] Slave I/O for channel '': Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. Error_code: 1593

```

之后,对应相应的错误码就可以去google或者百度一下了。如果你也遇到跟我一样的错误,那么网上肯定已经有相应的解决方法,如果你还没有找到的话,可以通过qq找到我。

还有一种查看错误信息的方式:

在从库里,

```

mysql> show slave status\G;

```

可以看到下面这两行信息:

```

mysql> show slave status\G

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

Slave_IO_State: Connecting to master

Master_Host: 192.168.1.107

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000045

Read_Master_Log_Pos: 107

Relay_Log_File: CENTOS6-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin.000045

Slave_IO_Running: Connecting

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

Relay_Log_Space: 107

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

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 1045

Last_IO_Error: error connecting to master 'repl@192.168.1.107:3306' - retry-time: 60  retries: 86400

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 0

row in set (0.00 sec)

```

```

Last_IO_Errno: 1045

Last_IO_Error:error connecting to master 'repl@192.168.1.107:3306' - retry-time: 60  retries: 86400

```

就可以看到了错误码是1045了。

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 211,639评论 6 492
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 90,277评论 3 385
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 157,221评论 0 348
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 56,474评论 1 283
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 65,570评论 6 386
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 49,816评论 1 290
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 38,957评论 3 408
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 37,718评论 0 266
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 44,176评论 1 303
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,511评论 2 327
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,646评论 1 340
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 34,322评论 4 330
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 39,934评论 3 313
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,755评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,987评论 1 266
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 46,358评论 2 360
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 43,514评论 2 348

推荐阅读更多精彩内容