MySQL入门笔记
版本选择：
5.x.20 以上版本比较稳定
一．MySQL的三种安装方式：
安装MySQL的方式常见的有三种：
· rpm包形式
· 通用二进制形式
· 源码编译
1．rpm包形式
1.1 MySQL官方提供的
（版本更新，修复了更多常见BUG）www.mysql.com/downloads
关于MySQL中rpm包类型的介绍：
MySQL-client 客户端组件
MySQL-debuginfo 调试MySQL的组件
MySQL-devel 对于MySQL编译安装PHP等依赖于MySQL的组件包
MySQL-embedded MySQL的嵌入式版本
MySQL-server 共享库
MySQL-shared 共享库
MySQL-shared-dompat 为了兼容老版本的共享库
MySQL-test MySQL的测试组件（在线处理功能）
安装方法：
首先可以从安装光盘中或者到mysql的网站上下载对应版本的rpm包如下：
MySQL-server-community-5.5.28-1.rhel5.i386.rpm
MySQL-client-community-5.5.28-1.rhel5.i386.rpm
接着我们可以使用rpm命令进行安装：
rpm -ivh MySQL-server-community-5.5.28-1.rhel5.i386.rpm
rpm -ivh MySQL-client-community-5.5.28-1.rhel5.i386.rpm
补充一点：
-h 使用符号＃显示安装进度
-v 报告每一步操作的情况
2．通用二进制包
2.1新建用户以安全方式运行进程：
# groupadd -r mysql －r：加入组ID号，低于499系统账号
# useradd -g mysql -r -s /sbin/nologin -M -d /mydata/data mysql
# chown -R mysql:mysql /mydata/data
-r, --system
Create a system account
-s, --shell SHELL
The name of the user's login shell.
-M
Do not create the user's home directory
-d, --home HOME_DIR
The new user will be created using HOME_DIR as the value for the user's
login directory.
-R, --recursive
operate on files and directories recursively 递归的
2.2安装并初始化mysql-5.5.28
首先下载平台对应的mysql版本至本地，这里是32位平台，因此，选择的为mysql-5.5.28-linux2.6-i686.tar.gz
# tar xf mysql-5.5.28-linux2.6-i686.tar.gz -C /usr/local
# cd /usr/local/
# ln -sv mysql-5.5.28-linux2.6-i686 mysql
# cd mysql
# chown -R mysql:mysql .
# scripts/mysql_install_db --user=mysql--datadir=/mydata/data
# chown -R root .
2.3为mysql提供主配置文件：
# cd /usr/local/mysql
# cp support-files/my-large.cnf /etc/my.cnf
2.4修改配置文件：
修改此文件中thread_concurrency的值为你的CPU个数乘以2，比如这里使用如下行：
thread_concurrency = 2
另外还需要添加如下行指定mysql数据文件的存放位置：
datadir = /mydata/data
2.5为mysql提供sysv服务脚本：
# cd /usr/local/mysql
# cp support-files/mysql.server /etc/rc.d/init.d/mysqld
2.6添加至服务列表：
# chkconfig --add mysqld
# chkconfig mysqld on
2.7启动服务测试使用
# service mysqld start
3．源码编译
3.1首先安装依赖包：
yum install -y cmake
yum –y install gcc gcc-c++ gcc-g77 autoconf automake zlib* fiex* libxml* ncurses-devel libmcrypt* libtool-ltdl-devel* make
3.2.添加用户及组:
#groupadd mysql
#useradd mysql -s /bin/nologin -M -g mysql
3.3创建所需目录并授权
mkdir -p /data/mysql/mysqlinstall
mkdir -p /data/mysql/mysqldata
chown -R mysql.mysql /data/mysql
3.4编译安装
#tar zxvf mysql-5.5.32.tar.gz
#cd mysql-5.5.32
cmake参数设置：
cmake -DCMAKE_INSTALL_PREFIX=/data/mysql/mysqlinstall --mysql安装路径
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock --mysql监听sock位置
-DDEFAULT_CHARSET=utf8 --mysql字符编码方式
-DDEFAULT_COLLATION=utf8_general_ci --默认排序规则
-DWITH_EXTRA_CHARSETS:STRING=utf8,gbk --额外支持字符集
-DWITH_MYISAM_STORAGE_ENGINE=1
-DWITH_INNOBASE_STORAGE_ENGINE=1 --安装INNOBASE存储引擎
-DWITH_MEMORY_STORAGE_ENGINE=1
-DWITH_READLINE=1 --快捷键功能
-DENABLED_LOCAL_INFILE=1 --允许从本地导入数据
-DMYSQL_DATADIR=/data/mysql --mysql数据存放位置
-DMYSQL_USER=mysql --mysql 安装用户名
-DMYSQL_TCP_PORT=3306 --mysql 监听端口
cmake -DCMAKE_INSTALL_PREFIX=/data/mysqlinstall -DDEFAULT_CHARSET=utf8
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS:STRING=utf8,gbk -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DENABLED_LOCAL_INFILE=1 -DMYSQL_DATADIR=/data/mysqldata -DMYSQL_USER=mysql -DMYSQL_TCP_PORT=3306
#make -j2 && make install -j2
3.5配置mysql
为mysql.5.5.32创建链接
#ln -sv /data/mysql/mysqlinstall /usr/local/mysql
-s, --symbolic
make symbolic links instead of hard links 软链接
-v, --verbose 显示过程
print name of each linked file
3.6 提供my.cnf
#cd /data/mysql/mysqlinstall
根据内存为mysql选择合适的配置文件模板
#cp mysqlinstall/support-files/my-small.cnf /etc/my.cnf
my.cnf 文件内容：
[client]
port = 3306
socket = /data/3306/mysql.sock
[mysqld]
port = 3306
socket = /data/3306/mysql.sock
basedir = /usr/local/mysql
datadir = /data/3306/data
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
skip-name-resolve
log-bin=mysql-bin
binlog_format=mixed
max_binlog_size = 500M
server-id = 1
[mysqld_safe]
log-error=/data/3306/ilanni.err
pid-file=/data/3306/ilanni.pid
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
3.7 配置环境变量
#echo 'export PATH=/data/mysql/mysqlinstall/bin:$PATH' >> /etc/profile.d/mysql.sh
#source /etc/profile
注意：添加环境变量时，一定要写在$PATH的前边，防止系统自带的mysql造成干扰!!!!!!!!!!!
3.8 msyql初始化
#cd scripts/
#./mysql_install_db --basedir=/data/mysql/mysqlinstall --datadir=/data/mysql/mysqldata --user=mysql
3.9 为mysql提供启动文件
#cp /data/mysql/mysqlinstall/support-files/mysql.server /etc/init.d/mysqld
#chmod +x /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig on mysqld
chkconfig --list
3.10 启动mysql
/etc/init.d/mysqld start (service mysqld start)
3.11 修改mysql 管理员密码
mysqladmin -uroot password 新密码
查看mysql支持的字符集
SHOW CHARACTER SET;
3.12 mysql 远程登录
grant all on *.* to root@'%' identified by'pwd@123'
////////////（.代表所有的数据库，当然也可以选择其他的数据库）
////////////（root 是用户名，一样也可以选择其他用户名）
////////////（%代表所有的ip地址）
////////////（这个‘password’里面的内容是root用户的密码）;
flush privileges; 刷新权限
3.13 mysql 添加用户
grant all privileges on test.* to z460@'%' identifiled by ‘password’
///////其中all privileges 表示所有的权限 ，当然我们也可以授予select ，update，drop ,create等权限
////////其中test 表示test数据库
////////z460表示 用户名
////////%表示所有的ip地址
////////password表示z460用户的密码
flush privileges ;
4. mysql 5.5多实例部署
mysql5.5数据库多实例部署，我们可以分以下几个步骤来完成。
1、 mysql多实例的原理
2、 mysql多实例的特点
3、 mysql多实例应用场景
4、 mysql5.5多实例部署方法
4.1 mysql多实例的原理
mysql多实例，简单的说，就是在一台服务器上开启多个不同的mysql服务端口（如3306，3307），运行多个mysql服务进程。这些服务进程通过不同的socket监听不同的服务端口，来提供各自的服务。
这些mysql实例共用一套mysql安装程序，使用不同的my.cnf配置文件、启动程序、数据文件。在提供服务时，mysql多实例在逻辑上看来是各自独立的，各个实例之间根据配置文件的设定值，来取得服务器的相关硬件资源。
4.2 mysql多实例的特点
4.2.1 有效的利用服务器资源
当单个服务器资源有剩余时，可以充分利用剩余的服务器资源来提供更多的服务。
4.2.2 节约服务器资源
当公司资金紧张，但是数据库需要各自提供独立服务，而且需要主从同步等技术时，使用多实例就最好了。
4.2.3 出现资源互相抢占问题
当某个实例服务并发很高或者有慢查询时，会消耗服务器更多的内存、CPU、磁盘IO等资源，这时就会导致服务器上的其它实例提供访问的质量下降，出现服务器资源互相抢占的现象。
4.3 mysql多实例应用场景
4.3.1 资金紧张型公司的选择
当公司业务访问量不太大，又舍不得花钱，但同时又希望不同业务的数据库服务各自独立，而且需要主从同步进行等技术提供备份或读写分离服务时，使用多实例是最好不过的。
4.3.2 并发访问不是特别大的业务
当公司业务访问量不太大，服务器资源基本闲置的比较多，这是就很适合多实例的应用。如果对SQL语句优化的好，多实例是一个很值得使用的技术。即使并发很大，只要合理分配好系统资源，也不会有太大问题。
4.4 mysql5.5多实例部署方法
4.4.1 mysql5.5多实例部署方法
mysql5.5多实例部署方法一个是通过多个配置文件启动多个不同进程的方法，第二个是使用官方自带的mysqld_multi来实现。
第一种方法我们可以把各个实例的配置文件分开，管理比较方便。第二种方法就是把多个实例都放到一个配置文件中，这个管理不是很方便。所以在此我们选择第一种方法，而且以下实验我们全部是在此方法下进行的。
4.4.2 mysql5.5的安装及配置
要配置mysql5.5多实例，我们首先要安装mysql5.5，有关mysql5.5的安装可以查看
mysql5.5安装完毕后，我们不要启动mysql，因为此时mysql是单实例的。
4.4.3 创建mysql多实例的数据目录
现在我们来创建mysql5.5多实例的数据目录，在此我们创建两个mysql实例3306和3307。创建各自的数据目录，如下：
mkdir -p[P9] /data/{3306,3307}/data
tree -L 2 /data/
4.4.4 修改mysql5.5多实例my.cnf文件
实例3306和3307的数据目录创建完毕后，我们来配置实例3306与3307的my.cnf配置文件。
复制mysql5.5安装目录support-files下的my-medium.cnf为my.cnf，并把内容修改为下。现在以3306这个实例为例，如下：
|
1 |
[client] |
注意图中***标记出来的部分，就是我们主要修改的，其他默认保持不变。
以上是实例3306的my.cnf配置文件，现在我们来配置实例3307的my.cnf。实例3307的配置文件my.cnf我们直接复制实例3306的my.cnf文件，然后通过sed命令把该文件中的3306修改为3307即可。如下：
cp /data/3306/my.cnf /data/3307/my.cnf
sed -i 's/3306/3307/g' /data/3307/my.cnf
或者
sed -e 's/3306/3307/g' /data/3306/my.cnf >/data/3307/my.cnf
4.5 初始化mysql多实例
实例3306和3307的my.cnf配置文件修改完毕后，我们需要来初始化这两个实例，使用mysql_install_db命令。如下：
scripts/mysql_install_db --basedir=/data/mysql/mysqlinstall --datadir=/data/3306/data --user=mysql
scripts/mysql_install_db --basedir=/data/mysql/mysqlinstall --datadir=/data/3307/data --user=mysql
注意mysql5.5的mysql_install_db在mysql5.5的/usr/local/mysql/scripts/mysql_install_db目录下。
查看实例初始化后的情况，如下：
tree -L 3 /data/
通过上图我们可以看到mysql实例在初始化后会创建基本的数据库。
现在再来看看初始化创建文件的属性，如下：
通过上图可以看到初始化创建的文件都是属于mysql这个用户的。
为什么会是这样呢？
这个是因为我们初始化加入--user=mysql这个选项。当然这个也是我们所需要的，因为这增加了mysql的安全性。
4.6 修改mysql实例的数据库目录权限
mysql实例初始化完毕后，我们现在把实例3306和实例3307的数据目录权限重新赋予给mysql用户。如下：
chown -R mysql:mysql /data/3306
chown -R mysql:mysql /data/3307
这个地方建议一定要操作一遍，否则在启动mysql实例时，会提示出错。导致mysql实例无法启动。
4.7 启动mysql5.5多实例
我们现在来启动实例。使用如下命令：
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3306/my.cnf &
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3307/my.cnf &
ps aux |grep mysqld
通过上图，我们可以看到实例3306和3307 都已经正常启动。也说明我们的mysql5.5多实例已经配置成功。
其实单实例mysql的启动也是通过mysqld_safe命令来启动。它默认会加载/etc/my.cnf文件。
4.8 登录mysql5.5多实例
登录多实例数据库时，我们需要加入该实例的socket文件，才能正常登录。现在以3306实例为例。
本地登录3306实例，如下：
mysql -uroot -p -S /data/3306/mysql.sock
本地登录进去后，我们在实例3306上创建一个ilanni3306的数据库。如下：
create database ilanni3306;
show databases;
现在我们远程登录实例3306，并查看刚刚新建的数据库。如下：
mysql -h192.168.1.213 -uroot -p -S /data/3306/mysql.sock
或者：mysql -h192.168.1.213 -uroot -p*** -P 3306
通过上图，我们可以看到远程也是可以连接3306实例的。
4.9 修改mysql5.5多实例root密码
修改实例3306的root密码，使用mysqladmin命令。如下：
mysqladmin -uroot password 123456 -S /data/3306/mysql.sock
到此我们的mysql多实例部署就已经完成。
二、MySQL数据库的基本操作
1.启动和关闭：
启动原理： mysqld 是脚本，最终调用mysqld_safe脚本
1.1 单实例：
/etc/init.d/mysqld start
或者 mysqld_safe --user=mysql & 此方法可以带参数
查看端口： ss –lnt |grep 3306
查看进程：ps –ef | grep mysql | grep –v grep
mysqladmin –uroot –p shutdown 优雅的关闭数据库
1.2 多实例启动：
mysqld_safe --defaults-file=/data/3306/my.cnf & 启动多实例数据库
mysqladmin –uroot –p –S /data/3306/mysql.sock shutdown 关闭
2.登录数据库
2.1 单实例：
mysql –uroot –p
2.1
多实例
mysql -uroot -p -S
/data/3306/mysql.sock
3.修改登录密码
3.1 不登录修改（简单不容易出错）
mysqladmin -uroot –p’…’ password ‘……’
mysqladmin -uroot –p’…’ password ‘……’ –S /data/3306/mysql.sock 多实例
3.2 登录修改：
# update mysql.user set password=password(“****”) where user=’root’ and host=’localhost’;
# flush privileges;
3.3 找回密码：
3.3.1 首先停止运行的数据库
3.3.2使用skip-grant-tables这个选项启动MySQL：
mysqld_safe --skip-grant-tables & 单实例
mysqld_safe –defaults-file=/data/3306/my.cnf --skip-grant-tables &
3.3.3现在可以不用密码进入mysql
> UPDATE mysql.user SET
password=password(“123456”) WHERE user='root' and host=’localhost’;
> FLUSH PRIVILEGES;
3.3.4重启mysql
4 数据库常见管理应用
4.1创建数据库
create database kangjie; (库名不以数字开头) 默认配置，字符自为拉丁字符集
- Ø show create database oldboyG 查看默认的字符集
创建oldboy_gbk的字符集数据库：
create database oldboy_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
校对规则：collate字符序
_ci表示大小写不敏感，_cs 表示大小写敏感，_bin表示用编码值进行比较。
create database oldb_utf8 default character set utf8 collate utf8_general_ci;
字符集不一致是数据库中文内容乱码的罪魁祸首。如果编译时制定了特定的字符集，则以后创建对应的数据库就不需要指定字符集。
企业中：创建数据库根据开发的程序确定字符集。建议UTF8。编译时指定字符集。建库时默认创建即可。
如果编译时制定了和程序不同的字符集，指定字符集创建数据库即可。
4.2 显示数据库：
show databases like ‘%oldboy%’;
drop database oldboy;
连接数据库： use oldboy_gbk;
select database();
select user();
select now();查看当前时间
show tables;
4.3 删除系统多余账号：
select user,host from mysql.user;
drop user “user”@”oldboy”;
如果drop不能删除（一般是特殊字符或大写）
可以使用： delete from mysql.user where user=’root’ and host=”oldboy’;
flush privileges;
处理用户，组好刷新一下权限。
4.4创建MySQL用户及赋予用户权限：
4.4.1 创建授权create user ‘kangjie’@’host’ identified by ‘password’ 其中host 指定用户在哪个主机上可以登录，如果是本地用户可以用localhost,如果可以从任意主机登录，可以使用%
grant privileges on databasename.tablename to ‘username’@’host’; 其中privileges 用户的操作权限:select,insert,update等，授权所有：all
grant all on *.* to ‘kangjie’@’%’;
运维人员常用的授权：
grant all on db1.* to ‘kangjie’@’localhost’ identified by ‘password’; 相当于上面的两条命令；
4.4.2 查看用户授权
查看用户权限： show grants for oldboy@localhost;
grant
****提示没有权限；
授权远程主机连接数据库：
grant all on*.* to ‘test’@’10.10.10.%’ identified by ‘test123’;
flush privileges;
登录： mysql –utest –ptest123 –h 10.10.10.0
交互式查询：mysql –utest1 –ptest1 –e ‘”show grants for test1@localhost;”
在授权是，可以授权用户最小的满足业务的需求的权限，不可一味授权all privileges;
4.4.3 企业中的授权：
常规情况下授权select ,insert ,update ,delete 4个权限即可，可能还需要create ,drop等比较危险的权限。
5.数据库表操作
5.1创建表
create table student (
id int(4) not null,
name char(20) not null,
age tinyint(2) not null default ‘0’,
dept varchar(16) default null
);
MySQL5.1和5.5环境默认的建表语句中的引擎不同，5.5默认Innodb
字段类型：
int[(M)] : 正常大小整数类型 4个字节
char(M) : 定长字符串类型，当存储时，总是用空格填满右边到达指定的长度。
varchar : 变长字符串类型。
5.2 查看建表的结构：
desc table
5.3 为表的字段创建索引
索引就像书的目录一样，如果在字段上建立的索引，那么可以以索引列为查询条件是可以加快查询的速度。
首先，创建索引都是在对应列上创建，也可以对多列创建索引。
5.3.1主键索引：
在建表时，增加主键索引的语句：
create table student(
id int(4) not null AUTO_INCREMENT,
name char(20) not null,
age tinyint(2) not null default ‘0’,
dept varchar(16) default null,
primary key[P10] (id),
KEY index_name(name)[P11]
);
建表后添加主键索引：alter (不建议这样做)
alter table student add primary key(id);
alter table student change id id int(4) not null auto_increment=1;
删除自增长的主键：
先删除自增长再删除主键：
alter table student change id id int(4);
alter table student drop primary key;
添加索引： alter table student add index index _name(name);
删除索引： alter table student drop index index_name;
也可以用：drop index index_name on student;
指定前n个字符创建索引：create index index_dept on student(dept(8));
创建联合索引：create index index_name_dept on student(name,dept);
联合索引的前缀特性：index(a,b,c) 仅a,ab,abc三个查询条件可以走索引
show index from student/G 查看索引
创建唯一索引（非主键）
create unique index uni_index_name on student(name);
索引列的创建及生效条件：
应该在哪些列上创建索引？
select user,host from mysql.user where host=…; 索引一定要创建在where 后的条件列上，而不是select后的选择数据的列。尽量选择在唯一值多的达标上建立索引
5.4 插入数据
insert
create table test(
id int(4) not null auto_increment,
name char(20) not null,
primary key(id)
) engine=innodb default charset=latin1;
insert into test(id,name) values(1,’test’);
insert into test values(2,’test2’);
insert into test(name) values(3,’test3’),(4,’test4’);
备份：逻辑备份，以sql语句导出
mysqldump –uroot –p*** -B oldboy > /opt/oldboy_bak.sql
过滤查看内容：
grep –E –v “#|/|^$|--” /opt/oldboy_bak.sql
错误案例
select * from kangjie where ***;
优化：insert 批量插入；
select不用*
多表查询：
使用explain查看查询计划：
可以判断语句是否走索引
update kangjie set name=test2 where id=1;
注意一定要加条件；否则数据全部改成了一样的内容！！！！
数据恢复：mysql –uroot –p*** -S /data/3306/mysql.sock </opt/oldbak.sql
增量恢复：55节1：39
data目录下
binlog日志用于记录所有更新了的数据或已经潜在更新了的数据
使用mysqlbinlog 查看二进制日志
在不能停库的情况下，先切割二进制日志：
mysqladmin –uroot –p*** /data/3306/mysql.sock flush-log
防止误操作的的实例：57节 1:2
老大登录数据库update一个记录，结果忘了加where，于是悲剧发生了
5.5 mysql防止误操作：
|
1 2 3 |
[oldboy_c64 ~]# mysql --help|grep dummy -U, --i-am-a-dummy Synonym for option --safe-updates, -U. i-am-a-dummy FALSE |
在mysql命令加上选项-U后，当发出没有WHERE或LIMIT关键字的UPDATE或DELETE时，mysql程序就会拒绝执行
2、指定-U登录测试
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
[oldboy_c64 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -U Welcome to the MySQL monitor. Commands end with ; or g. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> delete from oldboy.student; ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column mysql> quit Bye |
|
|
|
|
|
|
提示：不加条件无法删除，目的达到。
3、做成别名防止老大和DBA误操作
|
1 2 3 4 5 6 7 8 10 11 12 13 14 15 16 |
[oldboy_c64 ~]# alias mysql='mysql -U' [oldboy_c64 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock Welcome to the MySQL monitor. Commands end with ; or g. Type 'c' to clear the current input statement. mysql> delete from oldboy.student; ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column mysql> delete from oldboy.student where Sno=5; Query OK, 1 row affected (0.02 sec) mysql> quit Bye [oldboy_c64 ~]# echo "alias mysql='mysql -U'" >>/etc/profile [oldboy_c64 ~]# . /etc/profile [oldboy_c64 ~]# tail -1 /etc/profile alias mysql='mysql -U' |
结论：
在mysql命令加上选项-U后，当发出没有WHERE或LIMIT关键字的UPDATE或DELETE时，mysql程序拒绝执行
6.删除数据：
delete from test where id=6; 删除某一条数据
truncate table test; 清空一张表；
二者区别是：58节4：41
TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。
DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
6.3 TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。
对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。
TRUNCATE TABLE 不能用于参与了索引视图的表。
7.增删改表的字段
alter table oldboy add sex char(4);
alter table oldboy add age int(4) after name; 指定位置增加字段；
8.更改表名
rename table 原表名 to 新表名；
alter table oldboy rename to oldgirl;
9.删除表：
drop table test;
三、数据库备份
3.1备份单个数据库
3.1.1备份：mysqldump –uroot –p’Kangjie_456’ kangjie > /backup/$(date + %F).sql
恢复：先登录，创建数据库，然后倒入数据：
mysql –uroot –pKangjie_456 kangjie < /backup/a.sql
注意：备份是要加参数 –B ，自带建库和use库的语句
3.1.2倒入数据：mysql –uroot –pKangjie_456 < /backup/a.sql
3.1.3压缩备份：
mysqldump –uroot –pKangjie_456 -B kangjie|gzip > /backup/$(date +%F).sql.gz
3.1.4指定字符集备份：
--default-character-set=utf8
3.2 mysqldump原理：
mysqldump是把数据从数据库中已逻辑的sql语句的形式导出。逻辑导出效率不是很高。
导出时可能会出现乱码，一般导入时就正常。
导出时可以去掉注释。
3.3备份多个库：
-B参数，表示连接多个库，并且增加use db和create database db的信息。
mysqldump –uroot –p*** -B mysql kangjie |gzip> /a.sql.gz
分库备份
mysqldump –uroot –p*** -S /data/3306/mysql.sock –B kangjie |gzip > a.sql.gz
备份单个表：
mysql –uroot –p*** kangjie test > a.sql
grep –E –v “#|/|^$|--” /opt/oldboy_bak.sql
备份多个表：
mysql –uroot –p*** kangjie test1 test2 > a.sql
备份数据库结构：
3.3.1 -d 只备份表结构
精简备份内容：
--compact 让输出内容更少
ll –lrt 按照时间来显示
备份时锁表
--lock-all-tables, -x
提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭--single-transaction 和--lock-tables 选项。
mysqldump -uroot -p --host=localhost --all-databases --lock-all-tables
--lock-tables, -l
开始导出前，锁定所有表。用READ LOCAL锁定表以允许MyISAM表并行插入。对于支持事务的表例如InnoDB和BDB，--single-transaction是一个更好的选择，因为它根本不需要锁定表。
请注意当导出多个数据库时，--lock-tables分别为每个数据库锁定表。因此，该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。
mysqldump -uroot -p --host=localhost --all-databases --lock-tables
3.4 恢复数据：
1.source恢复：登入mysql，> source a.sql
2.命令恢复：gzip –d a.sql.gz 先解压
mysql –uroot –p*** < a.sql
非交互式对话：
-e 参数
mysql –uroot –p*** -e ‘show databases’
mysql –uroot –p*** -e ‘use kangjie;select * from test;’ | grep zhao
mysql –uroot –p*** -e ‘show processlist;’查看当前线程数
优化语句： 使用explian ，如果没有使用索引，则创建索引
show full processlist 看到更多的信息
非交互式内容查看方便，可以将信息导入到文件中查看。
show variables 查看变量
show global status 查看状况
mysql –uroot –p*** -e “show global status;”|egrep “Com_select|Com_insert|Com_delete|Com_update”
查看数据库增删改查的状况。
mysql sleep过多，修改time_out的时间
3.5不重启数据库修改数据库参数，但是要求重启后还能生效
mysql -uroot –p*** -S /data/3308/mysql.sock -e "show variables;"| grep key_buffer
mysql -uroot –p*** -S /data/3308/mysql.sock -e "set global key_buffer_size=32777218;"
然后修改配置文件，使得重启后生效。
通过echo实现交互式查询
echo “show databases;” | myqsl –uroot –p’***’ –S /data/3308/mysql.sock
chmod 1777 的意思 ？
3.6查看各种服务运行日志的习惯
1.服务日志如：mysql错误日志,执行tail -100 /data/3306/3306.err检查报错日志；
2.细看屏幕输出，不要忽略关键的输出内容；
3.查看系统的/var/log/messages；
4.如果是关联服务，同时查看相关的LOG；
四、主从同步
配置步骤：
1. 准备两台数据库环境；
2. 配置my.cnf文件，主库配置log-bin和server-id参数，从库配置server-id，不能和主库及其它从库一样，一般不开启log-bin功能，配置参数后重启生效。
3. 登录数据库增加用于从库连接主库同步的账户，例如：rep，并授权replication slave同步的权限。
GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.4.%' IDENTIFIED BY 'rep';
4. 登录主数据库，整库锁表，flush table with read lock;然后show master status 查看binlog的位置状态。
5. 新开窗口，备份或导出原有的数据库数据，并拷贝到从库所在的服务器目录。如果数据量很大，并且允许停机，可以停机打包，而不用mysqldump。
6.解锁主库，unlock tables;
7. 把主库导出的原有数据恢复到从库；
8.根据主库的show master status 查看binlog的位置状态，在从库执行change master to… 等语句
9. 从库开启同步开关， start slave
10. 从库show slave stausG,检查同步状态，并在主库进行更新测试。
1.锁表；
2.记录当前binlog位置信息；
3. 导出所有数据；
4.解锁；
MySQL 复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)。每个从服务器从主服务器接收主服务器已经记录到其二进制日志的保存的更新，以便从服务器可以对其数据拷贝执行相同的更新。
将主服务器的数据拷贝到从服务器的一个途径是使用LOAD DATA FROM MASTER语句。请注意LOAD DATA FROM MASTER目前只在所有表使用MyISAM存储引擎的主服务器上工作。并且，该语句将获得全局读锁定。
MySQL 使用3个线程来执行复制功能,其中1个在主服务器上，另两个在从服务器上。当发出START SLAVE时，从服务器创建一个I/O线程，以连接主服务器并让它发送记录在其二进制日志中的语句。
主服务器创建一个线程将二进制日志中的内容发送到从服务器。该线程可以识别为主服务器上SHOW PROCESSLIST的输出中的Binlog Dump线程。
从服务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件中，即中继日志。
第3个线程是SQL线程，是从服务器创建用于读取中继日志并执行日志中包含的更新。
有多个从服务器的主服务器创建为每个当前连接的从服务器创建一个线程；每个从服务器有自己的I/O和SQL线程。
4.1复制准备
4.1.1 定义服务器角色
主库mysql master 3306
从库 mysql slave 3307
4.1.2 数据库环境准备
（1）具备单机单数据库多实例的环境；
（2）两台服务器每个机器一个数据库的环境；
4.1.3数据库读法的约定
主库，master
从库，slave
shell脚本
MYUSER="root"
MYPASS="Kangjie_123"
MYSOCK=/data/3306/mysql.sock
MAIN_PATH=/backup/mysqlbackup
DATA_PATH=/backup/mysqlbackup
LOG_FILE=${DATA_PATH}/mysqllogs_`date +%F`.log
DATA_FILE=${DATA_PATH}/mysql_backup_`date +%F`.sql.gz
MYSQL_PATH=/data/mysql/mysqlinstall/bin
MYSQL_CMD="$MYSQL_PATH/mysql -u$MYUSER -p$MYPASS -S $MYSOCK "
MYSQL_DUMP="$MYSQL_PATH/mysqldump -u$MYUSER -p$MYPASS -S $MYSOCK oldboy -B --single-transaction --events -e"
cat |$MYSQL_CMD<< EOF
flush table with read lock;
system echo "---show master status result---" >>$LOG_FILE;
system $MYSQL_CMD -e "show master status"|tail -1 >>$LOG_FILE;
system ${MYSQL_DUMP} | gzip > $DATA_FILE ;
EOF
$MYSQL_CMD -e "unlock tables;"
推送
rsync -avz /backup/mysqlbackup/ -e 'ssh -p 52222' root@139.224.72.53:/backup
4.2 主库上执行操作
4.2.1设置server-id 值并开启binlog参数
根据mysql主从同步的原理，我们知道复制的关键因素就是binlog日志，所以，首先要打开binlog日志功能。
vim /data/3306/my.cnf 编辑my.cnf配置文件
[mysqld]
server-id=1
log-bin=/data/3306/mysql-bin
注意：
1.参数要放在my.cnf中的[mysqld]模块下；
2.server-id 的值0<id<255;
3.修改后重启数据库。
4.2.2 建立用于从库复制的账号rep
grant replication slave on *.* to ‘rep’@’10.10.10.%’ identified by ‘oldboy123’;
注意：replication slave为mysql同步的必须权限，此处不要授权all .
远程连接：mysql -urep -p*** -P 3307 -h 10.***
4.2.3 对数据库锁表只读（当前窗口不能关掉）
生产环境时，操作主从复制，需要申请停机时间。数据量很大会锁表会影响业务。
flush tables with read lock;
锁表时间会受到下面参数的限制
interactive_timeout = 60
wait_timeout = 60
show variables like ‘%timeout%’;
4.2.4查看当前binlog日志的位置
查看数据状态，即当前binlog日志文件名和二进制binlog日志偏移量。命令显示的信息要记录在案，后面的从库复制时是从这个位置开始的。
show master status;
File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 1256 |
当前的位置点；
4.2.5 导出数据库数据：
单开新窗口（原窗口关闭锁表就失效了），如果数据量很大（100G+），并且允许停机，可以停库直接打包数据文件迁移。
解锁数据库：unlock table;
原则上，导库期间，数据库没有数据插入，无特殊情况，binlog文件及位置点保持不变。
4.2.6 导入数据到备库
gzip –d new.sql.gz
4.2.7 修改备库的my.cnf
一般情况下，如果备库不再往下做同步，就关闭binlog日志
server-id修改为不同于主库
然后重启数据库
mysqladmin –uroot –p –S /data/3307/mysql.sock shutdown 关闭
mysqld_safe --defaults-file=/data/3307/my.cnf & 启动多实例数据库
4.2.8
在从库中执行：登录从库
CHANGE MASTER TO
MASTER_HOST=' 10.174.143.26',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='Kangjie_123',
MASTER_LOG_FILE='mysql-bin.000025',
MASTER_LOG_POS=323;
执行后，在/data/3307/data/目录下有master.info文件
操作原理是把用户密码等信息写入从库的master.info文件中。
不登录数据库，在命令行快速执行CHANGE MASTER的语句（适合在脚本中批量建slave库用）
cat |mysql –uroot –p’***’ –S /data/3307/mysql.sock <<EOF
CHANGE MASTER TO
MASTER_HOST='10.174.143.26',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='oldboy123',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1256;
EOF
执行同步开关：
slave start;
查看是否同步：show slave statusG
有三个关键性的参数：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
控制bin_log过期参数:
expire_logs_days = 7
mysqlbinlog 用来读取binlog日志
--flush-log让binlog切换文件
binlog会不断增加
从库中的relay-log.info 临时存放数据的文件。
3307-relay-bin.index 记录binlog日志。
4.3 生产环境主从库同步配置注意事项
1.申请设备资源，用作从库服务器；
2.方案文档和实施步骤；
4.3.1 工作时间轻松配置从库
模拟主从同步的步骤，获取到全备及全备过程中binlog位置的信息。
1.锁表备份全备一份；
2.锁表前后取得show master status 值记录日志里。
4.3.2 一键主从同步脚本
参数说明
--extended-insert, -e
Use multiple-row INSERT syntax that include several VALUES lists. This
results in a smaller dump file and speeds up inserts when the file is
reloaded.
mysqldump | gzip
gzip –d a.sql.gz 先解压
mysql –uroot –p*** < a.sql
五、相关mysql技术技巧概览
5.1 配置忽略权限库同步参数
必须忽略主从授权表；
information_schema数据库是MySQL自带的，它提供了访问数据库元数据的方式。什么是元数据呢？元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。有些时候用于表述该信息的其他术语包括“数据词典”和“系统目录”
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
---设置这两项就可以了---
不记录binlog日志
忽略记录binlog日志参数binlog-ignore-db=mysql一般用户系统的库和表
结论：
1.只有在【主】【从】库上分别设置replicate-ignore-db=mysql才可以做到从库不同步mysql库。
2.在主库上设置binlog-ignore-db=mysql不记录mysql库更新的binlog，来达到不同步mysql库的目的。
-----无法确定
使用replicate_do_db和replicate_ignore_db时有一个隐患，跨库更新时会出错（oldboy不推荐使用）
原因是设置replicate_do_db或replicate_ignore_db后，MySQL执行sql前检查的是当前默认数据库，所以跨库更新语句被忽略。
可以使用replicate_wild_do_table和replicate_wild_ignore_table来代替
如
replicate_wild_do_table=test.%
或
replicate_wild_ignore_table=mysql.%
这样就可以避免出现上述问题了
忽略mysql库和information_schema库的同步
replicate-ignore-db=mysql
看第六章：MySQL中的复制
show processlist 查看线程同步状态
Slave has read all relay log; waiting for the slave I/O thread to update it
5.2 mysql主从复制故障解决
模拟错误：模拟故障重现的能力是运维人员最重要的能力。
首先确保主从同步正常：show slave statusG
然后再从库中创建数据库abc；
接着在主库中再次创建该数据库，就会报错：
Last_Error: Error 'Can't create database 'kangjie123'; database exists' on query. Default database: 'kangjie123'. Query: 'create database kangjie123'
解决方法：
stop slave
set global sql_slave_skip_counter =1; 指针后移一位
start slave;
根据错误号，跳过错误
my.cnf中: slave-skip-errors = 1032,1062,1007
不全：
5.3 让MySQL slave库记录binlog方法
需要binlog的情况：
1.当前从库还要作为其他从库的主库。
2.把从库作为备份服务器是需要开启binlog；
在从库中加入参数：
log-slave-updates
log-bin=mysql3307-bin
expire_logs_days=7
服务运行情况下，不可以删除日志
一般情况下，从库用来做备份：
每天晚上做全备，然后是增量备份。
有了主从同步，还需要做全量+增量备份。如果主库有人为drop database * 操作。
5.4 严格设置从库只读
read-only的妙用.
1. –read-only 参数可以让从服务器只允许来自从服务器或具有super权限的用户更新。可以确保从服务器不接受来自用户端更新。
2. –read-only参数更新条件
a.具有super权限用户可以更新；
b.来自从服务器线程可以更新。
show slave status
添加参数：
3.--read-only 参数生产环境应用场景
可以在从库Slave中使用read-only参数，确保从库数据不被非法更新。
5.5 生产环境中如何确保从库只读
1.mysql 从服务器中加入read-only参数或者在从服务器启动时加该参数；
2.忽略mysql库及information_schema库同步。
3.授权从库用户仅为select 权限
查看同步状态：
mysql –uroot –pKangjie_123 –S /data/3307/mysql.sock –e “show slave statusG”|egrep “IO_Running|SQL_Running|Second”
查看用户授权：show grants for oldboy@localhost;
REVOKE ALL ON *.* FROM ‘oldboy’@’localhost’;
select user(); 查看用户
5.6 MySQL 主主同步
关键参数：
auto_increment_increment = 2 #自增ID的间隔，
auto_increment_offset=1 #ID的初始位置
Master 1：
auto_increment_increment = 2
auto_increment_offset = 1
log-bin=/data/3306/mysql-bin
log-slave-updates
server-id=1
Master2:
auto_increment_increment = 2
auto_inrement_offset = 2
log-bin=/data/3307/mysql-bin
log-slave-updates
server-id=1
cat |mysql –uroot –pKangjie_123 –S /data/3306/mysql.sock <<EOF
CHANGE MASTER TO
MASTER_HOST=' 10.174.152.178',
MASTER_PORT=3306,
MASTER_USER='repzhu',
MASTER_PASSWORD='Kangjie_123',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=3966;
EOF
5.* 查看数据库支持的引擎
InnoDB is the default storage engine as of MySQL 5.5.5
1.MySQL引擎概述
MySQL表存储结构是MySQL数据库重要组成部分：--MyISAM,InnoDB，NDBCluster。实际上，mysql不仅可以同时使用多种存储引擎，而且每种存储引擎和MySQL之间通过插件的方式使用。
1.1 MyISAM引擎：
MyISAM是MySQL关系数据库管理系统的默认存储引擎。这种MySQL表存储结构从旧的ISAM代码扩展出许多的功能。新版本mysql中，InnoDB引擎由于其对事务参考完整性，以及更高的并发性等优点开始逐步取代MyISAM引擎。
select version();查看数据库版本
每个MyISAM表都对应于硬盘上的三个文件。这三个文件有一样的文件名，扩展名不同:.frm 文件保存表的定义,但是这个文件不是MyISAM引擎的一部分，二十服务器的一部分， .MYD保存表的数据， .MYI 是表的索引文件。
1.2 MyISAM引擎特点
1.不支持事务。
2.表级锁（更新时锁整个表）：器锁定机制是表级锁，实现成本低但是也降低了并发性能。
3.读写互相阻塞：不仅会在写入时阻塞读取，MyISAM还会在读取是阻塞写入，但读本身不会阻塞另外的读。
4.只会缓存索引：MyISAM可以通过key_buffer缓存以大大提高访问性能减少磁盘IO，但是这个缓存区只会缓存索引，不会缓存数据。
5.对去速度较快，占用资源相对少；
6.不支持外键约束，但支持全文索引。
1.3 MyISAM引擎适用的生成业务场景
1.不需要事务支持的业务，一般为读数据比较多的网站应用。
2.并发相对较低的业务（锁定机制问题）；
3.数据修改相对较少的业务（阻塞问题）；
4.以读为主的业务，例如blog,图片的数据库等业务；
5.对数据一致性要求不是非常高的业务。
MyISAM引擎调优
2.1 InnoDB引擎特点：
1.支持事务：支持4个事务隔离级别，支持多版本读；
2.行级锁：通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响；
3.读写阻塞与事务隔离级别相关；
4.具有非常高效的缓存特性：能缓存索引，也能缓存数据。
5.整个表和主键以Cluster方式存储，组成一颗平衡树。
6.所有Secondary Index都会保存主键信息。
7.支持分区，表空间等；
8.支持外键约束，不支持全文索引。
2.2InnoDB引擎调优
1.主键尽可能小，避免给Secondary index 带来过大的空间负担
2.避免全表扫描，以为会使用表锁；
3.尽可能缓存所有的索引和数据，提高响应速度，减少磁盘IO消耗；
4.在大批量小插入的时候，尽量自己控制事务而不要使用autocommit自动提交。
5.合理设置innodb_flush_log_at_trx_commit参数值，不要过度追求安全性。
6.避免主键更新，这样会带来大量的数据移动。
六、MySQL生产备份实战
1.全量与增量备份
mysqldump –uroot –p*** -S /data/3306/mysql.sock –F –B oldboy|gzip > /server/mysqlbak_$(date +%F).sql.gz
-F刷新binlog日志；binlog日志就是MySQL的增量备份。
1.2 逻辑备份
利用mysqldump命令吧mysql数据备份成mysql语句存储。
单台数据库，使用rsync或者inotify，主从复制，增量把所有的binlog备份到远程服务器。
rsync -avz /data/3306/data/mysql-bin.0000* rsync_backup@10.0.0.18:backup --password-file=/etc/rsync.password
1.3如何增量恢复binlog日志：
把binlog日志转化成SQL语句，如果通过mysql汇付命令将sql语句还原到MySQL数据库中。
逻辑备份需要锁表不需要停库。
1.4 逻辑备份详解：
中小型公司，小于100G
-A 所有的数据库；
--compact 优化输出的内容的大小
-B 备份结果中加入use ，create databases等命令
--opt Same as –add-drop-table , --add-locks, --create-options, --quick, --extended-insert, --lock-tables,--set-charset, and –disable-keys. Enabled by default, disable with –skip-opt
--single-transaction InnoDB表在备份时，通常启用选项 –single-transaction 来保证备份的一致性，实际上他的原理是设定本次会话的隔离级别为：REPEATABLE READ,以确保本次会话时，不会看到其他会话已提交了对数据。
1.5 MyISAM引擎备份：
由于MyISAM引擎为表级锁，因此，在备份是需要放置在备份期间数据写入而导致不一致，所以要上锁
mysqldump –A –F –B --local-all-tables | gzip > /data/$(date +%F).tar.gz
http://imysql.cn/2008_10_24_deep_into_mysqldump_options
1.6 InnoDB引擎备份
由于InnoDB引擎为行锁row-level locking,因此备份时可以不对数据库加锁操作，可以加选项：--single-transaction
2.二进制日志：
备份MySQL的二进制日志：包括但不限于insert,delete,update,alter,create等，不包括select等查询的内容记录。
1 日志参数和日志格式：
查看二进制日志：(如果报错，则加入参数 --no-defaults)
mysqlbinlog --no-defaults /data/3306/mysql-bin.0000015
2 删除二进制日志
2.1 RESET MASTER; 删除所有的binlog日志，新日志编号从头开始；
2.2 PURGE MASTER LOGS TO ‘mysql-bin.010’; 删除10之前所有日志；
2.3 PURGE MASTER LOGS BEFORE ‘2013-03-05 24：45：56’; 删除之前产生的所有日志
2.4参数配置：expire_logs_days = 7
3.物理备份：
1.使用复制命令进行完全备份：
进行mysql物理备份时，希望备份数据保持一致，需要先对mysql执行锁表或停机等停止写入操作
1.1停止mysql服务器：
1.2 登录mysql加读锁；flush tables with read lock;
1.3 开启防火墙：
执行完之后，确认是否真的停止了写入
cd /data && tar zcf /data/mysqlbak_3306$(date +%F).tar.gz ./3306
1.4 xtrabackup
Xtrabackup是一款开源热备工具，支持mysql5.0以上的InnoDB,XtraDB,MyISAM集中存储引擎的数据，并且在备份时并不会锁定数据库。
4.分库分表备份
只需要还原一个库，一个表时，比较麻烦。
#!/bin/sh
for dbname in d3306 d3307 oldboy
do
mysqldump -uroot -poldboy -S /data/3306/mysql.sock -F -B ${dbname} |gzip > /server/backup/${dbname}_$(date +F%).sql.gz
七、MySQL增量恢复演示
1.创建一个数据库：
create database oldboy default character set gbk collate gbk_chinese_ci;
创建表：
use oldboy;
set names gbk;
create table student(
Sno int(10) NOT NULL COMMENT '学号',
Sname varchar(16) NOT NULL COMMENT '姓名',
Ssex char(2) NOT NULL COMMENT '性别',
Sage tinyint(2) NOT NULL default '0' COMMENT '学生年龄',
Sdept varchar(16) default NULL COMMENT '学生所在系别',
PRIMARY KEY (Sno)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
2.检查全备和增量备份
全量备份时，要刷新bin-log日志；
3.第一时间刷新binlog日志
立即刷新并备份binlog日志：mysqladmin –uroot –p*** -S /data/3306/mysql.sock flush-logs
目的是切断出问题前后的binlog
4.将全备和binlog备份重新备份到新的服务器；
最好全部的备份，防止无法挽回的错误。
5.恢复binlog生成sql语句
mysqlbinlog mysql-bin.000003 > bin.sql (如果有错误，加参数–no-defaults )
egrep –v “#|^$|^/|*”
注意：
5.1由binlog生成sql文件时，需要指定库名来生成：
mysqlbinlog (–no-defaults) –d olbdoy mysql-bin.000008 mysql-bin.000009 > bin.sql
-d 参数还是搞得不明白
5.2 指定开始时间和结束时间
mysqlbinlog mysqlbin.000001 --start-datetime=’2016-10-29 02:58:54’ --stop-datetime=’2016-10-30 03:45:34’ -r time.sql
指定开始时间到文件结束：
mysqlbinlog mysqlbin.000001 --start-datetime=’2016-10-29 02:58:54’ -d oldboy -r time.sql
指定文件开头到结束的时间：
mysqlbinlog mysqlbin.000001 --stop-datetime=’2016-10-29 02:58:54’ -d oldboy -r time.sql
注意：mysqlbinlog不会恢复截止时间恰好等于执行语句时间的SQL语句。
但是会恢复开始时间等于执行语句的SQL语句。
基于时间点的恢复，一般用于不是特别精确的恢复，并发高，可能有多条语句执行过了。
5.3 指定开始位置和结束位置
mysqlbinlog mysqlbin.000001 --start-position=510 --stop-position=1312 -r pos.sql
注意：结尾的位置不会被包含，即执行1312之前的语句。
6.恢复全量数据：
7.恢复增量数据：
注意：
查看binlog帮助文档
mysql -uroot -p*** -S /data/3306/mysql.sock oldboy < bin.sql 注意：需要添加库名
删除binlog日志中的导致错误语句
D:学习资料mysqlL042零起点自学-MySQL企业标准增量备份与故障排错 11课10:22s
D:学习资料linuxlinux基础L042零起点自学-MySQL企业标准增量备份与故障排错 15课
19课
八、MySQL优化
1.硬件优化
2.my.cnf参数优化
调优工具mysqlreport
http://www.day32.com/MySQL/tuning-primer.sh
3.SQL语句优化
4.架构优化
5.流程制度，安全优化
[P1]-g, --gid GROUP
The group name or number of the user's initial login group.
[P2]-r, --system
Create a system account
[P3]-s, --shell SHELL
The name of the user's login shell.
[P4]-M
Do not create the user's home directory
[P5]-d, --home HOME_DIR
The new user will be created using HOME_DIR as the value for the user's
login directory.
[P6]-R, --recursive
operate on files and directories recursively 递归的
- [P7]-j [N], --jobs[=N] Allow N jobs at once; infinite jobs with no arg.
[P8]-s, --symbolic
make symbolic links instead of hard links 软链接
-v, --verbose 显示过程
print name of each linked file
[P9]-p, --parents
no error if existing, make parent directories as needed
[P10]主键
[P11]字段普通索引