开发者社区> 科技小先锋> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

简介:
+关注继续查看

一、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,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
MySQL之最
最大和最小 一个表里最多可有1017列(在MySQL 5.6.9 之前最大支持1000列)。虚拟列也受限这个限制。 一个表最多可以有64个二级索引。 如果innodb_large_prefix打开,在InnoDB表DYNAMIC或COMPRESSED列格式下,索引前缀最大支持前3072字节;如果不打开的话,在任意列格式下,最多支持前767字节。
8506 0
后台(02)——MySQL(2)
探索Android软键盘的疑难杂症 深入探讨Android异步精髓Handler 详解Android主流框架不可或缺的基石 站在源码的肩膀上全解Scroller工作机制 Android多分...
1156 0
快速为MySQL创建大量测试数据
1. 引言 在PostgreSQL中可以用generate_series()函数来快速生成大量测试数据,在MySQL中没有提供类似的东西。那么在做测试的时候,要往表中插入大量数据库该怎么办?可以写一个循环执行INSERT语句的存储过程,但这种方式还是太慢,我试了下,1秒钟居然只能插500条记录。
976 0
MySQL之SQL分析三部曲实际案例(一)
附上MySQL之SQL分析三部曲地址http://blog.itpub.net/29510932/viewspace-1709732/ --------------------------------------------------------------...
1105 0
mysql 表操作
引用:http://i.mtime.com/1545224/blog/4774844/ MySQL中create table语句的基本语法是: CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]   [table_options] [select_statement]   TEMPORARY:该关键字表示用MySQL create table新建的表为临时表,此表在当前会话结束后将自动消失。
797 0
6963
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载