一、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 /]#
其它参数：
Mysql程序：语言设置
mysqld程序：通信、网络、信息安全
mysqld程序：内存管理、优化、查询缓存区
mysqld程序：日志
mysqld程序：镜像(主控镜像服务器)
mysqld程序：镜像(从属镜像服务器)
mysqld–InnoDB：基本设置、表空间文件
mysqld程序：InnoDB–日志
mysqld程序–InnoDB：缓存区的设置和优化
mysqld程序：其它选项
二、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 |
+
--------------------+
4
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 |
+
--------------------+
5
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
| |
+
-------+-------------+------+-----+---------+-------+
4
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 |
+
-------+------+------------+------------+
4
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 |
+
-------+------+------------+---------+
3
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 |
+
-------+------+------------+--------+
3
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 |
+
-------+------+------------+--------+
3
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的主从原理
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 |
+
--------------------+
5
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 |
+
--------------------+
5
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上已经创建好数据库，同时有论坛。要创建从数据库，这时应该先备份数据库，之后再导入到从(更改数据目录的时候，导入到主的新目录)。
