Mysql数据库--自学笔记--2
1.优雅关闭数据库
mysqladmin -uroot -p112233 shutdown
/etc/init.d/mysql stop
kill -USR2 'cat path/pid'
2.多实例mysql启动和关闭方法示例
启动：/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 >/dev/null &
关闭：${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.cock shutdown
3.强制Linux 不记录敏感的历史命令
#HISTCONTROL=ignorespace
4.环境变量的设置(改变提示符)
写入mysql的配置文件my.cnf中
[mysqld]
prompt \u@oldboy \r:\m:\s->
5.用户安全问题
delete from mysql.user;
grant all privileges on *.* system@'localhost' identified by '112233' with grant option
6.用户设置密码
命令行#
设置密码：mysqladmin -u root password'112233'
修改密码:
mysqladmin -u root -p '112233' password'123123'
mysqladmin -u root -p '112233' password'123123' -S /data/${port}/my.cock (多实例)
数据库>
update mysql.user set password=PASSWORD("112233")where user='root' and host='localhost';
提示：此法适合密码丢失后通过 --skip-grant-tables 参数启动数据库后修改密码
查看用户对应的密码
数据库>
select user,host,password from mysql.user;
flush privileges
7.单实例找回丢失的mysql root用户密码
1.首先停止mysql
/etc/init.d/mysqld stop
2.使用--skip-grant-tables启动mysql，忽略授权登陆验证。
mysqld_safe --skip-grant-tables --user=mysql &
mysql -u root -p
3.修改密码的方法：
update mysql.user set password=PASSWORD("112233")where user='root' and host='localhost';
8.多实例找回丢失的mysql root用户密码
1.关闭mysql
killall mysql
2.mysqld_safe --defaults-file=/data/${port}/my.cnf --skip-grant-tables &
3.修改密码的方法
update mysql.user set password=PASSWORD("112233")where user='root' and host='localhost';
4.修改密码报错方法处理：
路径问题导致找不到启动的脚本问题
cp /application/mysql/bin/mysqld_safe /application/mysql/bin/mysqld_safe.bak
sed -i 's#/usr/local/mysql#/application/mysql#g' /application/mysql/bin/mysqld_safe
9.sql语句
1.排序:
(升序)select user,host,password from mysql.user order by user asc;
(倒序)select user,host,password from mysql.user order by user desc;
2.删除用户：
delete from mysql.user where user='oldboy';
3.创建一个数据库：
create database huang;(安装时候没有指定字符集，那么默认是拉丁字符集数据库)
4.查看创建的数据库：
show create database db_name\G;
show databases like 'huang_gbk'
%huang% #%为通配符，匹配所有的内容
5.建立一个名为huang_gbk的GBK字符集数据库:
create database huang_gbk default character set gbk collate gak_chinese_ci;
6.建立一个名为huang_utf8的UTF-8字符集数据库:
create database huang_utf8 default character set utf8 collate utf8_general_ci;
7.编译的时候没有指定字符集或者指定了和程序不同的字符集，数据库已经在运行了，怎么解决？
指定字符集创建数据库即可。（上面的5.和6.即是方法）
8.删除数据库
drop database db_name;
9.连接数据库
use db_name;
查看当前连接的数据库: select db_name,; 查看数据版本：select version();
查看数据用户：select user(); 查看当前的时间：select now();
10.查看数据的表格
show tables;
10. 删除mysql系统多余的账号
drop user '用户'@'主机域';
如果drop删除不了（一般是特殊字符或者大写），可以用下面方式删除（以root用户，huang主机为例）
delete from mysql.user where user='root' and host='huang';
flush privileges;
11. 运维人员经常用grant命令创建用户同时进行授权：
grant all privileges on db_name.table_name to 'user'@'主机域' identified by 'password';
分开写：
create user '用户名'@'主机域' identified by 'password';
grant all on db_name.table_name to '用户名'@'主机域';
案例：创建huang用户，对test库具备所有权限，允许localhost主机登陆管理数据库，密码是password。
grant all privileges on test.* to huang@localhost identified by 'password';
查看授权情况：select user,host from mysql.user;
查看授权用户的具体权限：show grants for 'huang'@'localhost';
12. 授权局域网内主机远程连接数据库：
grant all on *.* test@'10.0.0.%' identified by 'password';
grant all on *.* test@'10.0.0.0/255.255.255.0' identified by 'password';
远程连接：mysql -uhuang -ppassword -h 10.0.0.1
用php服务器连接数据库的代码写法如下：
<?php
//$link_id=mysql_connect('主机名','用户','密码');
$link_id=mysql_connect('10.0.0.1','test','112233') or mysql_error();
if ($link_id){
echo "mysql successful by huang!";
}else {
echo mysql_error();
}
?>
13. 授权all privileges 后，要回收 select，insert，update，delete，这四个权限。
生成数据库表后，也要收回create,drop授权。
revoke insert,select,update,delete on *.* from '用户名'@'主机域';
生成数据库表后，也要收回create,drop授权。
revoke create on blog.* from '用户名'@'10.0.0.%';
14. 表格的操作
create table student(
id int(12) not null,
name char(20) not null,
age tinyint(2) not null default '0',
dept varchar(16) default null
);
查看表结构：desc db_table_name;
查看已建表的语句：show create table db_table_name\G
插入数据
15. 索引
主键索引
方法一、
create table student(
id int(12) not null AUTO_INCREMENT,
name char(20) not null,
age tinyint(2) not null default '0',
dept varchar(16) default null,
primary key(id),
KEY index_name (name)
);
提示：primary key(id) 是主键。
KEY index_name(name) 是字段普通索引。
优化：在唯一值多的列上建索引查询效率高。
方法二、
创建好表后,忘记加索引了：
先把原来的删除主键：
alter table student drop index index_name;
再利用alter命令修改id列为自增主键列
alter table student add index index_name(name);
创建前8个字符的索引
create index index_name on test(name(8));
创建联合索引
create index index_name_dept on test(name,dept);
创建联合索引(前N个字符创建的联合索引)
create index index_name_dept on test(name(8),dept(10));
创建唯一非主键索引
create unique index index_age on student(age);
创建主键索引
alter table student change id id int primary key auto_increment;
删除主键索引
alter table student drop primary key;
删除普通索引
alter table student drop index index_name;
drop index index_name on student;
索引疑问解决：
1.既然索引可以加快查询速度，那么就给所有的列建索引吧？
答：索引不但占用系统空间，更新数据时还需要索引数据的，因此，索引是一把双刃剑，并不是越多越好，
例如 ：数十个到几百行的小表格上无需建立索引，更新频繁，读很少的业务要少建立索引。
2.到底那些列上可以创建索引呢？
select user,host,from mysql.user where host=....,索引一定要创建在条件列，而不是select后的选择数据的列，
另外我们要尽量选择在唯一值多的大表上建立索引。
小结：1.要在表的列上创建索引
2.索引会加快查询速度，但是会影响更新的速度。
3.索引不是越多越好，要在频繁查询的where后的条件列上创建索引。
4.小表或唯一值极少的列上不建索引，要在大表以及内容多的列上创建索引。
16. 往表中插入数据
1.新建一个简单测试的表test
create table test(
id int(4) not null auto_increment,
name char(20) not null,
primary key(id)
);
2.插入数据
insert into test(id,name) values(1,'huang');
select * from test
insert into test(name) values('huang'); #由于id为自动增加，所以只在name列插入值。
insert into test values (1,'huang'),(2,'huang'),(3,'huang'),(4,'huang'),(5,'huang');#多条数据插入
17. 数据库备份
mysqldump -uroot -p112233 -B huang >/opt/huang_bak.sql
备份后检查备份的sql数据内容：过滤无用信息。
grep -E -v "#|\/|^$|--" /opt/huang_bak.sql
18. 查询数据
select * from test;
select id,name from test;
select id,name from test limit 2; #只查询两个
select id,name from test where id=1; #条件查询
select id,name from test where id=1 and name=sheng; #多个条件查询
select id,name from test where id>1 and id<5; #范围查询
select id,name from test order by id desc; #排序
注意：字符查询条件要加''引号。
多表查询：
select student.Sno,student.Sname,course.Cname,SC.Grade from student,course，SC where=student.Sno=SC.Sno and course.Cno=SC.Cno order by Sno;
查看sql语句是不是走了索引：
explain select * from test where name='huang'\G
19. 修改表中指定条件固定的数据
update test set name='gongli' where id=3; #单一数据修改
20. 防止误操作案例
http://blog.51cto.com/oldboy/1321061
21. 删除表中的数据
delete from test where id=1; #删除了一整列
truncate table test; #直接清空整个表 清空物理文件
delete from test; #也是清空整个表 一行行删
22. 增删改表的字段
命令语法：alter table 表名 add 字段 类型 其他;
alter table test add sex char(4);
alter table test add sex char(4) after name; #在name列后增加sex 列
alter table test add sex char(4) first; #第一位
21. 更改表名
rename table test to test1;
alter table test rename to test1;
22. 字符集和乱码问题
解决乱码问题方法如下：
1. set names latinl; #设置字符集为插入数据的表的字符集，然后插入中文数据。
source /tmp/test.sql #直接把一堆命令执行了。
2. mysql -u root -p112233 --default-character-set=latinl test < test.sql
3. mysql -u root -p112233 -e "set names latinl;select * from db_name.table_name;"
4. 更改my.cnf客户端模块的参数，解决乱码问题
[client]
character-set-server=latinl
表库字符集设置：
create database huang_utf8 default character set utf8 collate utf8_general_ci;
linux 系统字符集
cat /etc/sysconfig/i18n
LANG="zh_CN.utf8"
开发的程序字符集设置
简体UTF8
http://download.comsenz.com/DiscuzX/3.2/Discuz_X3.2_SC_UTF8.zip
show variables; #看mysql的变量
show global status; #查看mysql的状态
set global key_buffer_size=15k #设置mysql的变量
show variables like 'key_buffer%'; #查看设置的变量是不是生效了
指定字符集来建表
create table student(
id int(4) not null auto_increment,
name char(20) not null,
primary key (id)
)engine=InnoDB auto_increment=10 default charset=utf8
23. 工具 mysqldump ,mysqlbinlog,mysql,mysqladmin 自己学习下。
24. 查看常用字符集对应的信息
mysql -uroot -p112233 -e "show character set ;"|egrep "gbk|utf8|latinl"|awk '{print $0}'
25. 修改字符集的流程
对于已有的数据想改字符集不能通过 “alter database character set *”
或者 “alter table tablename character set *” 这两个命令没有更新已有记录的字符集，
而是对新创建的表或者记录生效。
已经有记录的字符的调整，必须先将数据导出，经过修改字符集后重新导入后才可以完成。
修改数据默认编码
alter database [your db_name] charset [your character setting]
下面模拟将latinl 字符集的数据库修改为GBK字符的实际过程
1. 导出表结构
mysqldump -uroot -p --default-character-set=latinl -d db_name>alltable.sql
--default-character-set=latinl #表示以GBK字符集进行连接
-d #只导表结构
2. 编辑 alltable.sql 将 latinl 改为 GBK
3. 确保数据库不再更新，导出所有数据
mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=latinl db_name>alltable.sql
--quick #用于转储大的表，强制mysqldump 从服务器一次一行的检索数据而不是检索所有的行，并输出前 cache 到内存中
--no-create-info #不创建 create table 语句
--extended-insert # 使用包括几个vlaues 列表的多行insert 语法，这样子文件更小，IO也小，导入数据时会非常快。
--default-character-set=latinl # 按照原有字符集导出数据，这样子导出的文件中，所有中文都是可见的，不会保存成乱码
4. 打开 alldata.sql 将set names latinl 修改 set names gbk;
5. 建库
create database db_name default charset gbk;
6. 创建表，执行 alltable.sql
mysql -uroot -p db_name < alltable.sql
7. 导入数据
mysql -uroot -p db_name < alldata.sql
总结：1.建库及建表的语句导出，sed批量修改为utf8
2.导出所有数据
3.修改mysql服务端和客户端编码为utf8
4.删除原有的库及建表语句
5.导入新的数据库及建表的语句
6.导入mysql的所有数据
26. 工具 mysqldump的使用和参数说明（数据库的备份，非常重要，非常重要，非常重要）
语法：mysqldump -uroot -p'112233' db_name > 备份数据库名
原理：利用mysqldump 命令备份数据的过程，实际上就是把数据从mysql 库里以逻辑的sql语句形式直接输出。
mysqldump -uroot -p'112233' -B huang |gzip >/tmp/mysql.bak.sql.gz
1. 导出数据用 -B
2. 用gzip对备份的数据进行压缩
备份所有的库：
mysqldump -uroot -p'112233' -B -A --events|gzip >/tmp/mysql_all.bak.sql.gz
-F #刷新binlog日志
--master-data=1 # 增加binlog日志文件名及对应的位置点
--compact #去掉注释，适合调试输出,生产不用。
-A #所有的库
-x,--lock-all-tables #锁表
-l # 只读锁表
-d #只备份表结构
-t #只备份表的数据
--singl-transaction #适合innodb事务数据库备份
innodb 表在备份时，通常启用选项 --singl-transaction来保证备份的一致性，实际上
它的工作原理是设定本次会话的隔离级别为：REPEATABLE READ,确保本次会话（dump）时，不会看的其他的会话已经提交的数据。
myisam:
mysqldump -uroot -p'112233' -B -A --master-data=1 -x --events|gzip >/tmp/all.sql.gz
innodb:推荐使用
mysqldump -uroot -p'112233' -B -A -master-data=2 --singl --events -transaction|gzip >/tmp/all.sql.gz
分库备份方法：
1. mysqldump -uroot -p'112233' -e "show databases;"|grep -Evi "database|infor|perfor"|sed -r 's#^([a-z].*$)#mysqldump -uroot -p'112233' --events -B \1|gzip >/tmp/\1.sql.gz#g'|bash
2. 用脚本的for循环执行命令。
网站：http://edu.51cto.com/course/course_id-808.html
分库恢复方法：
for db_name in 'ls *.gz|sed 's#_bak,sql.gz##g'';do mysql -uroot -p'112233' <${db_name}_bak.sql;done
一个库，分表备份：
多表备份语法： mysqldump -uroot -p'112233' 表名1 表名2 > 备份的文件名
单表备份语法： mysqldump -uroot -p'112233' 库名 表名 > 备份的文件名
只备份表结构：
mysqldump -uroot -p'112233' --campact -d huang(库) student(表)
只备份表的数据：
mysqldump -uroot -p'112233' --campact -t huang student
27. source命令恢复mysql数据：
mysql>source /tmp/mysql.bak.sql
28. mysql进程-状态-在线修改参数
show full processlist; #查看正在执行的完整sql语句，完整显示
show variables; #查看数据的参数信息，例如：my.cnf里的参数生效情况
show status; #当前会话的状态
show global status; #查看数据库运行状态信息，很重要，要分析并做好监控
set global key_buffer_size=32777218 #不重启数据库调整参数，直接生效，重启后失效
mysql数控批量插入数据shell脚本实现(案例)
http://oldboy.blog.51cto.com/2561410/597511
不登陆数据库执行mysql命令小结
http://oldboy.blog.51cto.com/2561410/632608
mysql sleep进程过多解决办法？？？
29. mysqlbinlog #解析mysql的binlog日志（路径：数据目录下，data目录下）
作用：用来记录mysql内部增删改查等对mysql数据库有更新内容的记录。
拆库：
mysqlbinlog -d 库名 mysql-bin.000020 >库名.sql
指定开始位置和结束位置(输出开始位置510到结束位置650的所有binlog日志到pos.sql)
mysqlbinlog mysql-bin.000021 --start-position=510 --stop-position=650 -r pos.sql
根据时间指定开始位置和结束位置
mysqlbinlog mysql-bin.000021 --start-datetime='2017-03-20 02:25:35' --stop-datetime='2017-03-21 03:25:35' -r time.sql
30. 数据库mysql主从复制机制
备份数据的方法：
1. NFS网络文件共享可以同步存储数据
2. Samba 共享数据：http://oldboy.blog.51cto.com/4633273/1203553
3. 定时任务或者守护进程结合 rsync,scp
4. inotify+rsync 触发式实时数据同步
5. FTP数据同步
6. ssh key+scp/rsync
7. svn 管理
mysql主从复制简介：
例一：mysql自动批量制作主从同步需要的语句
cat |mysql -uroot -p '112233' << EOF
CHANGE MASTER TO
MASTER_HOST='192.168.11.89',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='112233',
MASTER_LOG_FILE='mysql-bin.000025',
MASTER_LOG_POS=439;
EOF
实战：
主库master：192.168.11.89
1. 修改/etc/my.cnf 配置文件
[mysqld]
server-id = 1
log-bin = /data/master/mysql-bin
判断是不是打开了log-bin
show varviables like 'log_bin';
2. 添加验证账号：
grant replication slave on *.* to 'rep'@'192.168.11.%' identified by '112233';
flush privileges;
3. 主库备份，方便下次灌进从库。
在一个窗口锁库，不能写库，只能读库，不能关闭
flush table with read lock;
然后再执行：show master status; #获取 log-bin 那个点
另外一个窗口执行备份命令
mysqldump -uroot -p'112233' -B -A --events --master-data=1|gzip >/tmp/mysql_all.bak.sql.gz
最后解锁
unlocks tables;
从库slave：192.168.11.100
1. 修改/etc/my.cnf 配置文件
[mysqld]
server-id = 2
log-bin = /data/slave/mysql-bin
2. 上面第3步执行完后。把数据灌进从库
先把主库的备份文件传到从库下
scp /tmp/mysql_all.bak.sql.gz root@192.168.11.100:/tmp/
最后把数据灌进去
mysqldump -uroot -p'112233' </tmp/mysql_all.bak.sql.gz
3. 刷入同步需要的信息
CHANGE MASTER TO
MASTER_HOST='192.168.11.89',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='112233',
MASTER_LOG_FILE='mysql-bin.000025',
MASTER_LOG_POS=439;
上面的这些信息在 安装目录的/application/mysql/data/master.info
4.启动从库同步开关
start slave;
5. 最后看是不是正常工作了
show slave status\G
如果下面两个参数都是YES那么就正常了
Slave_SQL_Running:YES
Slave_IO_Running:YES
特别注意：
如果备份的时候用来了 --master-data=1 那么就不需要这两个配置参数了 MASTER_LOG_FILE='mysql-bin.000025', MASTER_LOG_POS=439;
31. 数据库mysql主从复制的问题集合
1. 主库 show master status 没结构，主库binlog 功能没有开，或者没有生效
shell#egrep "log-bin|server-id"
mysql>show varviables like 'server-id';
mysql>show varviables like 'log_bin';
提示：配置文件的参数和 show varviables 参数不一致。
2. 报错内容：Could not find first log file name in binary log index file.
MASTER_LOG_FILE='xxxxxxxx' 这个参数的问题。检查下
3. 非正常关闭的数据库，起不来了
rm -f /application/mysql/mysql.scok /application/mysql/*.pid
再重启
4. show varviables like '%timeout%';
显示(默认值)：interactive_timeout = 28800
wait_timeout = 28800
如果超过时间不操作，会自动解锁。
5. 由于切换 binlog 导致 show master status 位置变化无影响
32. mysql主从复制原理要点
1. 异步方式同步
2. 逻辑同步模式，多种模式，默认是通过SQL语句执行
3. 主库通过记录binlog实现对从库的同步。binlog记录数据库的更新语句
4. 从库1个IO线程。从库由一个IO线程和一个SQL线程实现
5. 从库关键文件master.info relay-log relay-info 功能。
6. 如果从库还想级联从库，需要打开log-bin 和log-slave-updates 参数。
33. 生产场景快速配置mysql主从复制方案
1. 安装好要配置的从库数据库，配置好log-bin和server-id参数。
2. 无需配置主库my.cnf文件，主库log-bin和server-id参数默认就是配置好的
3. 登陆主库增加用于从库连接的主库同步的账户例如：rep，并授权replication slave同步权限
4. 使用半夜mysqldump带 --master-data=1 备份的全部数据恢复到从库。
6. 从库开启同步开关，start slave。
7. 从库show slave status\G ,检查同步状态，并在主库进行测试更新。
34. 生产场景mysql主从复制读写分离授权方案及实战
1. 从库，连接用户授权上控制
主库：rep 112233 192.168.11.89 3306 (select,insert,delete,update)
从库：主库的rep用户同步到从库，然后回收insert，delete，update权限。
不收回从库权限，设置read-only参数确保从库只读。
主库：rep_w 112233 192.168.11.89 3306 (select,insert,delete,update)
从库：rep_r 112233 192.168.11.100 3306 （select）
风险：rep_w 连接从库
设置read-only参数确保从库只读。
mysql库不同步：进行如下授权
主：rep 112233 192.168.11.89 3306 (select,insert,delete,update)
从：rep 112233 192.168.11.100 3306 (select)
缺陷：从库切换主库时候，连接用户权限问题。保留一个从库专门准备接替主。
实战：
主库：忽略授权表/etc/my.cnf
replicate-ignore-db = mysql
binlog-ignore-db = mysql
binlog-ignore-db = performance_schema
binlog-ignore-db = information_schema
设置read-only参数确保从库只读。
[mysqld]
read-only
2. web程序写的指向从库 （读指向从库）程序或者代理
3. 让从库只能读，不能写
35. 主从故障原因和解决方案
1. 从库已经有一个库，但是主库又创建了同名字的库，那么就冲突了
解决方法1：
stop slave;
set global sql_slave_skip_counter = 1;
start slave;
解决方法2：
根据错误号跳过指定的错误
slave_skip_errors = 1032,1062,1007
2. mysql 连接慢的时候，加这个参数 skip-name-resolve
36. 让mysql从库记录binlog方法
1. 把当前的从库还要作为其他的从库的主库，也就是级联同步。
2. 把从库作为备份服务器时，需要开启binlog
做法:
log-slave-update
log-bin=mysql-bin
expire_logs_days = 7 #find /data/mysql/ -type f -name "mysql-bin.000*" -mtime +7|xargs rm -f
37. 一主多从。如果主库宕机了。mysql服务挂了或者服务器宕机
1. 登陆从库 show processlist; 看两个线程的更新状态
2. 登陆从库 查看 /application/mysql/data/master.info
确保更新完毕，看哪个是更新最多内容的（POS最大的）。就把它作为主库处理。
或者利用半同步功能，直接选择做了实时同步到这个从库，太子的位置。
3. 确保所有relay log全部更新完毕
在每个从库执行stopslave io_thread; show processlist;
查看Has read all relay log; 表示从库更新都执行完毕了
4. 登陆 从库
stop slave;
reset master;
quit
5. 清理之前那些read-only 和授权表。
6. 进到数据数据目录，删除master.info relay-log.info
cd /application/mysql/data
rm -f master.info relay-log.info
7. 提升从库为主库
开启：log-bin = mysql-bin
如果存在log-slave-updates read-only等一定要注释它
到此为止，提升主库完毕
8. 如果主库服务器没有宕机，需要去主库拉取binlog 补全提升为主库的从库的数据。
9. 其它从库操作
已检查（同步user rep都存在）
登陆从库
stop slave;
change master to master_host ='192.168.11.100'
start slave;
show slave status\G
=========================主库宕机切换成功。
10. 修改程序配置文件从数据库指向100
平时访问数据库用域名，则直接可以修改hosts解析
38. 主库和从库有计划的切换。
1. 主库锁表
2. 登陆所有的库查看同步状态，是否完成
39. 从库宕机了
1. 从做slave
直接灌数据：
stop slave;
gzip -d xxxxx.sql.gz
mysql -uroot -p'112233' < xxxxx.sql
change master to master_host='192.168.11.100',master_user='rep',
master_password='112233',master_log_file='mysql-bin.00001',
master_log_pos=62358;
start slave;
show slave status\G
40. 两台mysql互为主备
41. mysql数据库增量恢复大总结
1. 人为sql造成的误操作
2. 全备和增量
3. 恢复时建议对外停止更新
4. 恢复全量，然后把增量日志中有问题的sql语句删除，恢复到数据库
增量恢复的核心思想：
1. 流程制度控制，防止问题发生。如果不做，面临服务和数据，鱼和熊掌不可兼得。
2. 延迟备份来解决。或者通过监控，白名单，黑名单机制。
3. 业务需求容忍度，可量化的目标，根据需求选择停库或者锁表或者容忍丢失部分数据。
42. 主从同步出现延迟，（从库记录慢了）怎么解决?
43. 数据库的读写分离软件，mysql-proxy amoeba;
44. mysql-mmm架构的高可用软件。
45. mysql半同步应用
46. mysql+heartbeat+brbd 高可用。
http://oldboy.blog.51cto.com/2561410/1240412
47. xtrabackup 物理热备份
48. 错误日志：记录mysql服务进程mysqld在启动/关闭/运行过程中错误信息
[mysql_sage]
log-error=/data/application/mysql/localhos.err
49. 查询日志 ：分为两类 普通查询日志 和 慢查询日志
1. 普通查询日志参数：记录客户端连接的信息和执行的sql语句信息
mysql> show variables like 'general_log%';
+------------------+---------------------------------------+
| Variable_name | Value |
+------------------+---------------------------------------+
| general_log | OFF |
| general_log_file | /application/mysql/data/localhost.log |
+------------------+---------------------------------------+
2. 慢查询日志：记录执行时间超出制定值（long_query_time）的sql语句
long_query_time = 1
log-slow-queries = /data/application/mysql/slow.log
log_queries_not_using_indexes
3. 二进制日志 ：记录数据被修改的相关信息
mysql> show variables like 'log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | ON | #记录binlog
| log_bin_trust_function_creators | OFF | #临时不记录binlog
+---------------------------------+-------+
50. binlog 日志三种模式
1. Statement Level模式 (默认模式)
mysql> show variables like '%binlog_format%';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
2. Row Level模式
用下面的命令查看该模式下的日志文件记录：
mysqlbinlog --base64-outpout=decode-rows -v mysql-bin.000016
3. Mixed 混合模式（前两种模式的结合）
修改方式：
1. 配置文件/etc/my.cnf
log-bin=mysql-bin
#binlog_format="STATEMENT"
#binlog_format="ROW"
#binlog_format="MIXED"
2. 运行时，在线修改立即生效
mysql> set SESSION binlog_format = 'MIXED';
全局生效
mysql> set GLOBAL binlog_format = 'MIXED';
51. mysql服务存储引擎
1. 分类：
MyISAM 和 InnoDB
不同的引擎功能，占用的空间大小，读取性能等可能有区别
mysql> show create table stu\G
*************************** 1. row ***************************
Table: stu
Create Table: CREATE TABLE `stu` (
`name` char(20) DEFAULT NULL,
`dept` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
2. [root@localhost]#ll /application/mysql/data/mysql
user.frm #文件保存表的定义
user.MYD #保存表的数据
user.MYI #表的索引文件
[root@localhost /application/mysql/data/mysql]# file user.frm
user.frm: MySQL table definition file Version 9
[root@localhost /application/mysql/data/mysql]# file user.MYD
user.MYD: Hitachi SH big-endian COFF executable, not stripped
[root@localhost /application/mysql/data/mysql]# file user.MYI
user.MYI: MySQL MISAM compressed data file Version 1
3. MyISAM引擎的特点
4. MyISAM引擎适用的生产业务场景
5. MyISAM引擎调优精要
6. InnoDB引擎的特点
7. InnoDB引擎适用的生产业务场景
8. InnoDB引擎
9. InnoDB引擎
52. 事务介绍
1. 事务四大特性：
原子性 一致性 隔离性 持久性
2. 事务的开启
start transcation #开启事务
rollback #回滚事务
commit #提交事务
mysql> show variables like '%autocom%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
set autocommit=OFF
set autocommit=ON
53. 创建后引擎的更改
alter table huang ENGINE=INNODB;
alter table huang ENGINE=MyISAM;
批量修改mysql引擎：
mysql_convert_table_format --user=root --password=112233 -- engine=MyISAM huang stu;
54. heartbeat介绍与作用
别名：
heartbeat2 默认使用这条命令添加VIP
ifconfig eth0:1 192.168.11.200/24 up
ifconfig eth0:1 192.168.11.200/24 down
辅助ip：
keepalved 和 heartbeat 用这个命令添加：
ip addr add 10.0.0.1/24 broadcast 10.0.0.255 dev eth1
ip addr del 10.0.0.1/24 broadcast 10.0.0.255 dev eth1
注意：ip add 可以查看包括别名和辅助ip，用ifconfig 不能查看辅助ip 的情况
55. heartbeat 脚本默认目录
常用的配置文件有三个，分别为
ha.cf 参数配置文件
authkey 认证文件 高可用服务器之间个呢进对端的authkey ,对对端进行认证
haresource 资源配置文件，如配置启动ip 资源 及脚本程序 服务等
启动脚本：/etc/init.d
资源目录：/etc/ha.d/resource.d/ 如果以后自己开发程序，就放在这个地方，然后再haresource文件之间调用
56. heartbeat 快速部署搭配过程
1. ip规划和配置
master： eth0 10.0.0.7 eth1 10.0.10.7 VIP 10.0.0.17
slave： eth0 10.0.0.8 eth1 10.0.10.8 VIP 10.0.0.18
2. 配置主机名和hosts
#data-1-1
hostname data-1-1
sed -i 's#HOSTNAME=moban#HOSTNAME=data-1-1#g' /etc/sysconfig/network
#data-1-2
hostname data-1-2
sed -i 's#HOSTNAME=moban#HOSTNAME=data-1-2#g' /etc/sysconfig/network
sed -i '/^10.0.0/d' /etc/hosts #删除原来的10.0.0.*
uname -n 主机名一定要跟这个命令得出的结果一样
3. 配置服务器的心态连接，让两台的网卡eth1 直连：
#data-1-1
/sbin/route add -host 10.0.10.8 dev eth1
echo '/sbin/route add -host 10.0.10.8 dev eth1 >>/etc/rc.local'
#data-1-2
/sbin/route add -host 10.0.10.7 dev eth1
echo '/sbin/route add -host 10.0.10.7 dev eth1 >>/etc/rc.local'
4. 安装Centos 6.5 heartbeat3.0软件
#下载并安装epel 包
mkdir -p /data/tools
cd /data/tools
wget http://mirrors.ustc.edu.cn/fedora/epel/6/x86_64/epel-release-6-8.noarch.rpm
rpm -ivh epel-release-6-8.noarch.rpm
rpm -qa |grep epel
#安装heartbeat
yum -y install heartbeat*
cd /usr/share/doc/heartbeat-3.0.4/
cp ha.cf haresources authkeys /etc/ha.d/
#查看配置文件ha.cf
cat /etc/ha.d/ha.cf
#the start by huang
debugfile /var/log/ha-debug
logfile /var/log/ha-log
logfacility local10
keeplive 2
deadtime 30
warntime 10
initdead 120
#bcast eth1
mcast eth1 255.0.0.7 694 1 0
auto_failback on
node data-1-1
node data-1-2
crm on
#the end by huang
#配置文件 authkeys
chmod 600 /etc/ha.d/authkeys #不改权限 ，heartbeat 服务报错无法开启服务
cat /etc/authkeys
auth 1
1 sha1 key-for-sha1-any-text-you-want
#配置文件haresources
#huang services
#10.0.0.17 www.etiantian.org
data-1-1 IPaddr::10.0.0.17/24/eth0
#data-1-1 IPaddr::10.0.0.17/24/eth0 httpd
#10.0.0.18 bbs.etiantian.org
data-1-2 IPaddr::10.0.0.18/24/eth0
配置hosts
cat >>/etc/hosts <<eof
10.0.10.7 data-1-1
10.0.10.8 data-1-2
eof
5. 启动heartbeat 服务
chkconfig iptables off
setenforce 0
sed -i 's#SELINUX=enforcecing#SELINUX=disable#g' /etc/selinux/config
/etc/init.d/heartbeat start
ps -ef |grep heartbeat
chkconfig heartbeat off
chkconfig drbd off
6. heartbeat实现web 服务高可用案例
详情请看word文档 “heartbeat 实际搭配过程”
57. DRBD介绍（实时数据同步，mysql的工具）
不同主机之间的基于块设备的镜像。
基于 beatheart 来搭建
Cetos6.5 快速部署drbd8.4:
1. 环境搭建
双网卡，双硬盘
2. 配置好ip
3. 对磁盘分区
#大于2T 硬盘 parted 分区
#data-1-1
parted /dev/sdb mklabel gpt
parted /dev/sdb mkpart primary 0 1024
parted /dev/sdb p
parted /dev/sdb mkpart primary 1025 2146
parted /dev/sdb p
#分区大小不同是有目的的，为后面扩容做准备
#对新添加的磁盘快速分区方法：
# echo -e "n\np\n1\n\n+10G\nn\np\n2\n\n+20G\nw" |fdisk /dev/sdb
# partprobe
两台机器都需要分区
4. 安装配置DRBD
下载地址：http://rpmfind.net/
mkdir -p /data/tools
cd /data/tools
wget -q http://elrepo.org/elrepo-release-6-5.el6.elrepo.noarch.rpm
rpm -ivh elrepo-release-6-5.el6.elrepo.noarch.rpm
sed -i 's#keepcache=0#keepcache=1#g' /etc/yum.conf
yum -y install drbd kmod-drbd84
yum install -y kernel-devel kernel-headers flex brdb84-utils kmod-drbd84
rpm -qa |grep drbd
5. 安装DRBD并加载内核
mkdir /data/tools -p
cd /data/tools
export LC_ALL=C
lsmod |grep drbd
modprobe drbd
echo "modprobe drbd >/dev/null 2>&1" >/etc/sysconfig/modules/drbd.modules
6. 配置文件drbd.conf
global {
#minor-count 64;
#dialog-refresh 5; #5 seconds
#dialog-ip-verififcation;
usage-count no;
}
common {
protocol C;
disk {
no-io-error detach;
no-disk-flushes;
no-md-flushes;
}
net {
sndbuf-size 512k;
#timeout 60; # 6 seconds (unit = 0.1 seconds)
#connect-int 10; # 10 seconds (unit = 1 seconds)
#ping-int 10; # 10 seconds (unit = 1 seconds)
#ping-timeout 5; # 500 ms (unit = 0.1 seconds)
max-buffers 8000;
uplug-watermark 1024;
max-epoch-size 8000;
# ko-cout 4;
#allow-two-primaries;
cram-hmac-alg "shal";
shared-secret "hdhwXes23sYEhart8t";
after-sb-Opri disconnect;
after-sb-1pri disconnect;
after-sb-2pri disconnect;
rr-conflict disconnect;
# data-integrity-alg "md5";
# no-tcp-cork;
}
syncer {
rate 330M;
al-extents 517;
}
}
resource data {
on data-1-1 {
device /dev/drbd0;
disk /dev/sdb1;
address 10.0.10.7:7788;
meta-disk /dev/sdb2 [0];
}
on data-1-2 {
device /dev/drbd0;
disk /dev/sdb1;
address 10.0.10.8:7788;
meta-disk /dev/sdb2 [0];
}
}
7. 初始化drbd
drbdadm create-md data
drbdadm up data
cat /proc/drbd
8. 设置主，同步数据到对端
#data-1-1上执行，不能再data-1-2上执行
drbdadm -- --overwrite-data-of-peer primary data
9. 挂载写入数据
#data-1-1上执行
mkfs.ext4 -b 4096 /dev/drbd0
tune2fs -c -1 /dev/drbd0
mkdir /md1
mount /dev/drbd0 /md1
for n in 'seq 10';do /bin/cp /bin/cat /md1/huang$n;done
cat /proc/drbd
10. 备节点查看数据
drbdadm down data
mount /dev/sdb1 /mnt
ls /mnt
11. 备节点还原服务
umount /mnt
drbdadm up data
cat /proc/drbd
chkconfig drbd off
chkconfig heartbeat off
58. mysql高可用需求与架构 heartbeat 和 brdb 的结合使用。
1. 修改配置文件haresources
主节点：data-1-1 IPaddr::10.0.0.17/24/eth0 drbddisk::data Filesystem::/dev/drbd0::/data::ext4
备节点：data-1-1 IPaddr::10.0.0.17/24/eth0 drbddisk::data Filesystem::/dev/drbd0::/data::ext4
2.主备都启动 heartbeat
/etc/init.d/heartbeat stop
/etc/init.d/heartbeat start
3. 查看是否成功
ip add |grep 10.0.0
cat /proc/drbd
df -h
4. 演示主备切换（备节点接管大概一分钟时间）
主节点：/etc/init.d/heartbeat stop 或者reboot #主节点故障了
备节点：tail -f /var/log/ha-debug #查看接管过程。
5. 裂脑解决方法
a. 在从节点如下操作
modprobe drbd
drbdadm secondary data
drbdadm disconnect data
drbdadm -- --discard-my-data connect data
b. 在主节点上，通过cat /proc/drbd查看状态，如果不是WFConnection状态，需要手动连接
drbdadm connect data
6. /usr/share/heartbeat/hb_standby
/usr/share/heartbeat/hb_takeover local
7. 修改配置文件haresources
主节点：data-1-1 IPaddr::10.0.0.17/24/eth0 drbddisk::data Filesystem::/dev/drbd0::/data::ext4 mysql
cp /data/3306/mysql /etc/ha.d/resource.d/ #把mysql的启动脚本放在这个目录下
chmod +x /etc/ha.d/resource.d/mysql
8. 检查下所有的状态
a. ip add |grep 10.0.0.17
b. cat /proc/drbd
c. df -h
d. lsof -i :3306
9. 注意问题
my.cnf 里面的配置。指定的路径要一样、免得出现各种错误。
10. 故障后，修复后，主备切换回来
保证主的状态是 Secondary 再切回来
/usr/share/heartbeat/hb_takeover local
59. 网站很卡，很慢
1. 网站出问题，很慢 (数据库导致的)。
a. show full processlist;
mysql -uroot -p'112233' -e "show full processlist;"|grep -vi sleep
b. 慢查询语句（日志文件）
long_query_time = 1
log-slow-queries = /data/3306/slow.log
c. 发现大量的慢查询语句
explain select xxxxxxxxxxxxxxxxxxxxxxxxx
select SQL_NO_CACHE xxxxxxxxxxxxxxxxxxxxxxxxxxx
找到原来是没有索引引起的。
d. 查看表结构：show create table ad_oldbooy_detail\GBK
e. 查看条件字段列的唯一性
select count(distinct * ) from ad_oldboy_detail;
f. 解决方法（建立联合索引）
create index d_a_p on ad_oldbooy_detail(dateline,ader(20),pos(20));
2. 数据负载很高 （网页搜索导致的）
解决方案请看 ---企业面试题集合文档
60. mysql 数据优化思想和优化实践
1. 硬件优化
a. CPU 一台机器8-16颗CPU
b. 内存 32-64G 跑两个实例
c. 硬盘 数量越多越好。性能：ssd（高并发）>sas（普通业务）>sata(线下)
d. 网卡 多块网卡bond 以及buffer tcp 优化
2. 软件优化
操作系统：64位
软件：mysql 编译安装和优化
3. my.cnf里参数的优化
优化的幅度很小。大部分机构和SQL语句优化
思想： 监控 一边监控一边改参数
show global status\G
性能调优工具：mysqlreport 自动分析参数
http://www.day32.com/MySQL/tuning-primer.sh
4. SQL语句的优化
a. 索引优化
1）抓成慢SQL，配置my.cnf
long_query_time = 2
log-slow-queries = /data/3306/slow-log.log
2)慢查询日志分析工具---mysqlsla(建议用)
mysqldumpslow mysqlsla myprofi mysql-explain-slow-log mysqllogfilter
3）每天晚上0点定时分析慢查询，发到核心开发，DBA分析，高级运维 CTO的邮箱里。
b. 大的复杂的SQL语句拆分成多个小的sql语句
子查询 JOIN 连表查询 。
c. 数据库是存储数据的地方，但不是计算数据的地方
对数据计算应用类处理。都要拿到前端应用解决。禁止在数据库上处理
d. 搜索功能 like %老男孩%， 一般不要用mysql数据库
5. 架构的优化
a. 业务拆分：搜索功能 like %老男孩%， 一般不要用mysql数据库
b. 数据库前端必须要加cache 例如：mencached 用户登陆，商品查询
c. 某些业务应用使用nosql 持久化存储，例如：mencachedb,redis,ttserver.
粉丝关注，好友关系等等。
d. 动态的数据静态化。整个文件静态化，页面片段静态化。
e. 数据库的集群和读写分离。一主多从，双主多从。通过程序或者dbproxy 进行集群读写分离
f. 单表超过2000万。拆库拆表。
6. 流程制度，制度，安全优化
任何一次人为数据库记录的更新，都要走流程
a. 人的流程：开发-->核心开发-->运维或者DBA
b. 测试流程：内网测试-->IDC测试-->线上执行
c. 客户端管理，PHPMYADMIN
61. 运维是一场没有硝烟的战争。
http://blog.51cto.com/oldboy/1296694
posted on 2018-11-28 10:27 huanglinsheng 阅读(129) 评论(0) 编辑 收藏 举报
