构建Mysql三部曲之三 主从案例

本文涉及的产品
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介:

一、Mysql配置文件解读

(以mysql-server-5.1.73-3.el6_5.x86_64版本为例)

Mysql配置文件一般位于/etc/my.cnf,默认配置如下
[root@jacken /]# cat /etc/my.cnf 
[mysqld]
datadir=/var/lib/mysql  ##从给定目录读取数据库文件
socket=/var/lib/mysql/mysql.sock ##为MySQL客户程序与服务器之间的本地通信指定一个套接字文件(仅适用于UNIX/Linux系统; 默认设置一般是/var/lib/mysql/mysql.sock文件)
user=mysql ##mysqld程序在启动后将在给定UNIX/Linux账户下执行; mysqld必须从root账户启动才能在启动后切换到另一个账户下执行; 
mysqld_safe脚本将默认使用user=mysql选项来启动mysqld程序
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0 ##符号链接数据库或表可以存储在my.cnf中指定datadir之外的分区或目录

[mysqld_safe]
log-error=/var/log/mysqld.log  ##错误日志位置
pid-file=/var/run/mysqld/mysqld.pid ##为mysqld程序指定一个存放进程ID的文件(仅适用于UNIX/Linux系统); 
[root@jacken /]#

其它参数:

wKiom1UmjmfD-KpgAAK0ptQtfKc079.jpg

Mysql程序:语言设置

wKioL1Umj7PRVEzrAAEIf-GdRVo577.jpg

mysqld程序:通信、网络、信息安全

wKiom1UmjnfBN5PRAAZMrKpWnIw851.jpg

mysqld程序:内存管理、优化、查询缓存区

wKiom1UmjoHgGTP-AAWJKXzSCow820.jpg

mysqld程序:日志

wKiom1Umjo2C9QA8AAYg9Q9MC7o942.jpg

mysqld程序:镜像(主控镜像服务器)

wKioL1UmkRaz5pr-AAFNFdfGE4Y664.jpg

mysqld程序:镜像(从属镜像服务器)

wKiom1Umj9zy6CRlAAiJ2LJkUFM056.jpg

mysqld–InnoDB:基本设置、表空间文件

wKioL1UmkTjjSeIGAAdAfAAiW28932.jpg

mysqld程序:InnoDB–日志

wKioL1UmkUDjUSC8AASop4wNufQ400.jpg

mysqld程序–InnoDB:缓存区的设置和优化

wKiom1Umj_zh_6YlAAJtvP7rZQ8437.jpg

mysqld程序:其它选项

wKioL1UnupuQm-MmAAMedR8ynIk850.jpg

二、Mysql的增、删、改、查、备份数据库

增    insert into 表名(字段名,字段名) values (值,值);

删    delete from 表名 where 条件(oracal中就可以不要from)

改    update 表名 set 字段=值,字段=值 where 条件

查    select 字段,字段 from 表名 where 条件

创建一个数据库名字为jacken

mysql> show databases;

1
2
3
4
5
6
7
8
9
+ --------------------+
Database            |
+ --------------------+
| information_schema |
| discuz             |
| mysql              |
| test               |
+ --------------------+
rows  in  set  (0.00 sec)

mysql> create database jacken;

Query OK, 1 row affected (0.02 sec)

mysql> show databases;

1
2
3
4
5
6
7
8
9
10
+ --------------------+
Database            |
+ --------------------+
| information_schema |
| discuz             |
| jacken             |
| mysql              |
| test               |
+ --------------------+
rows  in  set  (0.00 sec)

在jacken数据库中创建一张表,名字为mytable,包含name、sex、birth、love

1
2
3
4
5
6
7
8
9
10
11
mysql> use jacken;
Database  changed
mysql>  create  table  mytable( name  varchar (20),sex  char (1),birth  date ,love  varchar (20));
Query OK, 0  rows  affected (0.01 sec)
mysql> show tables;
+ ------------------+
| Tables_in_jacken |
+ ------------------+
| mytable          |
+ ------------------+
1 row  in  set  (0.00 sec)

mysql> desc mytable;

1
2
3
4
5
6
7
8
9
10
+ -------+-------------+------+-----+---------+-------+
| Field | Type        |  Null  Key  Default  | Extra |
+ -------+-------------+------+-----+---------+-------+
name   varchar (20) | YES  |     |  NULL     |       |
| sex   |  char (1)     | YES  |     |  NULL     |       |
| birth |  date         | YES  |     |  NULL     |       |
| love  |  varchar (20) | YES  |     |  NULL     |       |
+ -------+-------------+------+-----+---------+-------+
rows  in  set  (0.05 sec)
mysql>

在mytable中增加以下数据

name  sex  birth love

user1    M   1992-12-20 soccer

user2    F    2003-03-03 music

user3    M   1995-09-10 basketball

user4    F    1998-08-09 reading

1
2
3
4
5
6
7
mysql>  insert  into  mytable  values
     -> ( "user1" , "M" , "1992-12-20" , "soccer" ),
     -> ( "user2" , "F" , "2003-03-03" , "music" ),
     -> ( "user3" , "M" , "1995-09-10" , "basketball" ),
     -> ( "user4" , "F" , "1998-08-09" , "reading" );
Query OK, 4  rows  affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from mytable;

1
2
3
4
5
6
7
8
9
+ -------+------+------------+------------+
name   | sex  | birth      | love       |
+ -------+------+------------+------------+
| user1 | M    | 1992-12-20 | soccer     |
| user2 | F    | 2003-03-03 | music      |
| user3 | M    | 1995-09-10 | basketball |
| user4 | F    | 1998-08-09 | reading    |
+ -------+------+------------+------------+
rows  in  set  (0.00 sec)

删除mytable中的user3数据

mysql> delete from mytable where name='user3';

1
2
3
4
5
6
7
8
9
10
Query OK, 1 row affected (0.00 sec)
mysql>  select  from  mytable;
+ -------+------+------------+---------+
name   | sex  | birth      | love    |
+ -------+------+------------+---------+
| user1 | M    | 1992-12-20 | soccer  |
| user2 | F    | 2003-03-03 | music   |
| user4 | F    | 1998-08-09 | reading |
+ -------+------+------------+---------+
rows  in  set  (0.00 sec)

把user4的爱好改为sleep

mysql> update mytable set love="sleep" where name="user4";

1
2
3
4
5
6
7
8
9
10
11
Query OK, 1 row affected (0.00 sec)
Rows  matched: 1  Changed: 1  Warnings: 0
mysql>  select  from  mytable;
+ -------+------+------------+--------+
name   | sex  | birth      | love   |
+ -------+------+------------+--------+
| user1 | M    | 1992-12-20 | soccer |
| user2 | F    | 2003-03-03 | music  |
| user4 | F    | 1998-08-09 | sleep  |
+ -------+------+------------+--------+
rows  in  set  (0.00 sec)

把mytable名字改为mytable_two

mysql> alter table mytable rename to mytable_two;

1
2
3
4
5
6
7
8
Query OK, 0  rows  affected (0.00 sec)
mysql> show tables;
+ ------------------+
| Tables_in_jacken |
+ ------------------+
| mytable_two      |
+ ------------------+
1 row  in  set  (0.00 sec)

mysql> select * from mytable_two;

1
2
3
4
5
6
7
8
+ -------+------+------------+--------+
name   | sex  | birth      | love   |
+ -------+------+------------+--------+
| user1 | M    | 1992-12-20 | soccer |
| user2 | F    | 2003-03-03 | music  |
| user4 | F    | 1998-08-09 | sleep  |
+ -------+------+------------+--------+
rows  in  set  (0.00 sec)

备份数据库jacken到/data下

1
2
3
4
5
[root@jacken ~] # ls /data/
[root@jacken ~] # mysqldump -h localhost -u root -p jacken > /data/jacken.db
Enter password: 
[root@jacken ~] # ls /data/
jacken.db

三、Mysql主从配置

mysql的主从原理

wKioL1Un4Ivjx0cKAAEyJ4YFUQk697.jpg

   Mysql主从同步其实是一个异步复制的过程,要实现复制首先需要在master上开启bin-log日志功能,整个过程需要开启3个线程,分别是Master开启IO线程,slave开启IO线程和SQL线程。

1、在从服务器执行slave start,从服务器上IO线程会通过授权的用户连接上master,并请求master从指定的文件和位置之后发送bin-log日志内容。

2、Master服务器接收到来自slave服务器的IO线程的请求后,master服务器上的IO线程根据slave服务器发送的指定bin-log日志之后的内容,然后返回给slave端的IO线程。(返回的信息中除了bin-log日志内容外,还有本次返回日志内容后在master服务器端的新的binlog文件名以及在binlog中的下一个指定更新位置。)

3、Slave的IO线程接收到信息后,将接收到的日志内容依次添加到Slave端的relay-log文件的最末端,并将读取到的Master端的 bin-log的文件名和位置记录到master-info文件中,以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log的哪 个位置开始往后的日志内容,请发给我”;

4、Slave的Sql线程检测到relay-log中新增加了内容后,会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容,并在自身执行。

配置主从注意事项

1、在master上开启bin-log日志功能,记录更新、插入、删除的语句。

2、必须开启三个线程,主上开启io线程,从上开启io线程和sql线程。

3、从上io线程去连接master,master通过io线程检查有slave过来的请求,请求日志、postsion位置。

4、master将这些相应的日志返回给slave,slave自己去下载到本地的realy_log里面,写入一个master-info

日志记录同步的点。

5、slave的sql线程检查到realy-log日志有更新,然后在本地去exec执行。

6、主从同步是属于异步方式。

环境介绍:

master

IP:192.168.1.2  Servername jacken  已经构建lamp平台,且已安装discuz(参见 lamp搭建的两种方式)

slave 

IP:192.168.1.3  Servername lamp 已经安装好数据库

修改Master Mysql数据库my.cnf配置文件内容如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[root@jacken ~] # cat /etc/my.cnf 
[mysqld]
datadir= /data/mysql
socket= /var/lib/mysql/mysql .sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks  
symbolic-links=0 
log-bin=mysql-bin  
server- id  = 1 
auto_increment_offset=1    
auto_increment_increment=2
[mysqld_safe]  
log-error= /var/log/mysqld .log  
pid- file = /var/run/mysqld/mysqld .pid   
replicate- do -db =all
[root@jacken ~] #

创建/data/mysql数据目录,启动mysql

1
2
3
[root@jacken ~] # mkdir -p /data/mysql
[root@jacken ~] # chown -R mysql:mysql /data/mysql
[root@jacken ~] # /etc/init.d/mysqld restart

修改Slave Mysql数据库my.cnf配置文件内容如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[root@lamp ~] # cat /etc/my.cnf 
[mysqld]  
datadir= /data/mysql  
socket= /var/lib/mysql/mysql .sock  
user=mysql 
#Disabling symbolic-links is recommended to prevent assorted security risks  
symbolic-links=0 
log-bin=mysql-bin  
server- id  = 2 
auto_increment_offset=2    
auto_increment_increment=2
[mysqld_safe]  
log-error= /var/log/mysqld .log  
pid- file = /var/run/mysqld/mysqld .pid  
master-host =192.168.1.2 
master-user=tongbu 
master-pass=123456 
master-port =3306 
master-connect-retry=60 
replicate- do -db =all
[root@lamp ~] #

在Master数据库服务器上设置权限,查看那Position,执行如下命令:

1
2
3
4
5
6
7
8
9
10
11
12
mysql>  grant  replication slave  on  *.*  to  'tongbu' @ '%'  identified  by  '123456' ;
Query OK, 0  rows  affected (0.00 sec)
mysql> flush  privileges ;
Query OK, 0  rows  affected (0.00 sec)
mysql> show master status;
+ ------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------+----------+--------------+------------------+
| mysql-bin.000003 |      335 |              |                  |
+ ------------------+----------+--------------+------------------+
1 row  in  set  (0.00 sec)
mysql>

然后在slave服务器指定master IP和同步的position点:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> change master  to  master_host= '192.168.1.2' ,
     -> master_user= 'tongbu' ,
     -> master_password= '123456' ,
     -> master_log_file= 'mysql-bin.000003' ,
     -> master_log_pos=335;
Query OK, 0  rows  affected (0.07 sec)
mysql> slave start;
Query OK, 0  rows  affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: Waiting  for  master  to  send event
                   Master_Host: 192.168.1.2
                   Master_User: tongbu
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000003
           Read_Master_Log_Pos: 335
                Relay_Log_File: mysqld-relay-bin.000002
                 Relay_Log_Pos: 251
         Relay_Master_Log_File: mysql-bin.000003
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
               Replicate_Do_DB: 
           Replicate_Ignore_DB: 
.....


Slave_IO_Running: Yes

Slave_SQL_Running: Yes

两个状态为YES,代表slave已经启动两个线程,一个为IO线程,一个为SQL线程。

在Master上建立一个数据库ms_test

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql>  create  database  ms_test;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+ --------------------+
Database            |
+ --------------------+
| information_schema |
| discuz             |
| ms_test            |
| mysql              |
| test               |
+ --------------------+
rows  in  set  (0.00 sec)

在Slave上去查看

1
2
3
4
5
6
7
8
9
10
11
mysql> show databases;
+ --------------------+
Database            |
+ --------------------+
| information_schema |
| discuz             |
| ms_test            |
| mysql              |
| test               |
+ --------------------+
rows  in  set  (0.00 sec)

主从同步完成

自此Mysql主从搭建完毕,现在有一个问题,如果master服务器down机了,如何快速恢复服务呢?

可以通过两种方法:

第一种方法,如果程序连接的是master的IP,直接在slave服务器上添加master的IP即可。这个手动去操作,而且需要花费时间比较长,可能还会出现误操作的情况,不推荐。

第二种方法,可以使用keepalived、heartbeat作为HA检测软件,检查MySQL服务是否正常,不正常则自动切换到slave上,推荐使用

第二种方法会在后续文章中更新

这里先说下第一种,手动操作。

1、模拟Master关闭

1
2
3
[root@jacken /] # /etc/init.d/mysqld stop
Stopping mysqld:                                           [  OK  ]
[root@jacken /] #

2、Slave数据库必须启动,在slave上授权网站IP对数据库的访问权限。

1
2
3
4
mysql>  grant  all  on  *.*  to   root@ '192.168.1.2'  identified  by  '123456' ;
Query OK, 0  rows  affected (0.00 sec)
mysql> flush  privileges ;
Query OK, 0  rows  affected (0.00 sec)

3、修改网站服务器config目录下:

config_global.php config_ucenter.php 把原先master ip改成slave ip地址。

把config_global.php的这一行改为:

$_config['db']['1']['dbhost'] = '192.168.1.3:3306';

把config_ucenter.php这一行改为:

define('UC_API', 'http://192.168.1.3/uc_server');

4、重启httpd服务

1
2
3
4
[root@jacken config] # /etc/init.d/httpd restart
Stopping httpd:                                            [  OK  ]
Starting httpd:                                            [  OK  ]
[root@jacken config] #

提示:

在config_global.php中

$_config['db']['1']['dbhost'] = 'localhost';

如果主服务器好了,要切换回去,这里必须写为localhost

主从同步后错误的解决方法(一旦出现错误将不会再同步master)

第一种:

该方法适用于主从库数据相差较小,或者要求数据并非完全统一的情况

1、在slave上,stop slave;

2、在slave上 set global sql_slave_skip_counter =1;(1代表的是事物)

3、在slave上,start salve;

之后再用show slave status\G  查看:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

ok,现在主从同步状态正常了

第二种:

该方法适用于主从库数据相差较大,或者要求数据完全统一的情况

1、先进入主库,进行锁表,防止数据写入,进行数据备份 

1
mysql> flush tables  with  read  lock;

2、把数据备份到mysql.bak.sql文件

1
[root@jacken ~] #mysqldump -uroot -p -hlocalhost > mysql.sql

3、查看master 状态

1
mysql> show master status;

4、把mysql备份文件传到从库机器,进行数据恢复

1
[root@jacken ~] #scp mysql.sql root@192.168.1.3:/tmp/

5、停止从库的状态

1
mysql> stop slave;

6、到从库执行mysql命令,导入数据备份

1
mysql> source /tmp/mysql.sql

7、设置从库同步,注意该处的同步点,就是主库show master status信息里的| File| Position两项

1
mysql> change master  to  master_host =  '192.168.1.2' , master_user =  'tongbu' ,master_password= '123456' , master_log_file =  'mysqld-bin.000001' , master_log_pos=272;

8、重新开启从同步

mysql> start slave;

9、查看同步状态

1
2
3
mysql> show slave status\G  查看:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

两个YES,主从同步正常。

特别提醒:

如果在master上已经创建好数据库,同时有论坛。要创建从数据库,这时应该先备份数据库,之后再导入到从(更改数据目录的时候,导入到主的新目录)。



本文转自Jacken_yang 51CTO博客,原文链接:http://blog.51cto.com/linuxnote/1631050,如需转载请自行联系原作者
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
MySQL 关系型数据库
|
关系型数据库 MySQL Linux
|
关系型数据库 MySQL Shell
|
2月前
|
SQL 存储 数据管理
阿里云视觉智能开放平台的逻辑数仓基于统一的SQL语法
【2月更文挑战第9天】阿里云视觉智能开放平台的逻辑数仓基于统一的SQL语法
52 2
|
3月前
|
存储 关系型数据库 MySQL
在阿里云的AnalyticDB MySQL版中使用CREATE TABLE语句来创建内表
在阿里云的AnalyticDB MySQL版中使用CREATE TABLE语句来创建内表【1月更文挑战第16天】【1月更文挑战第78篇】
208 3
|
4月前
|
关系型数据库 MySQL BI
用友畅捷通基于阿里云 EMR StarRocks 搭建实时湖仓实战分享
本文从用友畅捷通公司介绍及业务背景;数据仓库技术选型、实际案例及未来规划等方面,分享了用友畅捷通基于阿里云 EMR StarRocks 搭建实时湖仓的实战经验。
594 0
用友畅捷通基于阿里云 EMR StarRocks 搭建实时湖仓实战分享
|
4月前
电子好书发您分享《阿里云云原生一体化数仓新能力解读》
电子好书发您分享《阿里云云原生一体化数仓新能力解读》
262 2
|
1月前
|
分布式计算 关系型数据库 OLAP
阿里云AnalyticDB基于Flink CDC+Hudi实现多表全增量入湖实践
阿里云AnalyticDB基于Flink CDC+Hudi实现多表全增量入湖实践
73 0
|
4月前
|
SQL BI Apache
奇富科技基于阿里云数据库 SelectDB 版内核 Apache Doris 的统一 OLAP 场景探索实践
Apache Doris 作为整体 OLAP 场景,助力奇富科技信贷科技服务平台优化,使得报表分析场景 SLA 达标率提升至 99% 以上,平均查询耗时降低 50%,为营销活动、广告投放等提供强有力的数据支持。
奇富科技基于阿里云数据库 SelectDB 版内核 Apache Doris 的统一 OLAP 场景探索实践
|
4月前
电子好书发您分享《阿里云云原生一体化数仓新能力解读》
电子好书发您分享《阿里云云原生一体化数仓新能力解读》
74 1