mysql运维思想
-
1. 授权开发人员mysql权限。
主库：
Grant select,insert,update,deleteon webmysql.* to user@”10.1.1.%” identified by“password”;
从库：
Grant select onwebmysql.* to user@”10.1.1.%” identified by“password”;
-
1. 生产环境读写分离账户设置：
主库（提供写服务）：webmysql（数据库） user（账户） ip：10.1.1.21 port 3306
从库（提供读服务）：webmysql（数据库） user（账户） ip：10.1.1.22 port 3306
-
2. 数据库备份。
从库备份要打开binlog，备份包括全备和binlog增量备份。
-
3. 创建数据库指定字符集：
GBK:create database darren DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
UTF8: CREATE DATABASE darren DEFAULT CHARACTER SET utf8 COLLATEutf8_general_ci;
-
4. 查看数据库的用户：
selectuser,host from mysql.user;
drop user'root'@'localhost'; 删除root用户
deletefrom mysql.user 删除全部用户
删除用户时有大写，用drop是删不掉的，需要用delete
deletefrom mysql.user where user=’Root’ and host=’localhost’;
grant allprivileges on *.* to system@’localhost’ identified by ‘mysqlpassword’ withgrant option （创建新的数据库管理员账号，with grantoption的意识是使system账户有赋予其他账号的权限。）
另一种授权的方法：
先创建用户：create user Darren@’localhost’identified by ‘password’;
再授权：grant all on *.* to Darren@’localhost’;
收回权限：
REVOKEdelete on wordpress.* FROM ‘wordpress’@’localhost’;
创建表：
createtable test (id int(4) not null primary key auto_increment,name char(20) notnull);
desc 表名 ：查看表结构
showcloumns from 表名
showcreate table mysql.表名\G
show indexfrom 表名 ：查看索引
建表后添加索引：
altertable 表名 add index 索引名（name）
createindex 索引名 on 表名（name：列）--不能创建主键索引
Show grantfor system@‘localhost’ （查看system账号的权限。）
Selectuser（）；当前用户。
Showcreate database mysql; 查看创建mysql数据库的字符集类型。
select *from darren.test 查看数据库的表。
showprocess list 查看mysql当前的线程数（用户执行的mysql语句）
注：语句如果停留太久说明数据库有问题，需要优化。显示全部的话用show full processlist
mysql –uroot –p ‘password’ –e “show proesslist;” | grep xxx或者>xxx.log
注：-e的好处是可以过滤一些不需要的东西
show variables 显示数据库中的配置，包括my.cnf中的配置是否生效。
show global status 查看数据库当前的状态。-包括在线查询人数，缓存用量，插入数据的人数，删除数据的人数。
set global server_id=2 不重启数据使修改数据库参数，让其生效。如果想重启还生效需要改配置参数。
远程连接数据库：
mysql –uroot –p ‘password’ –P 3307 –h 10.1.1.21
查看mysql现在已提供什么存储引擎:
mysql> show engines;
看你的mysql当前默认的存储引擎:
mysql> show variables like '%storage_engine%';
你要看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎):
mysql> show create table 表名;
-
5. 刷新mysql系统权限
flushprivileges;
showcreate databases oldboy \G;
-
6. 数据库的备份
Mysqldump–u root –p ‘password’ oldboy（数据库名）>/backup/$(date+%F).sql
恢复：注意如果没有要恢复的数据库要先创建数据库
Createdatabase oldboy
Mysql –uroot –p ‘password’ oldboy</backup/2016.sql
Mysqldump–u root –p ‘password’ –B oldboy>/backup/$(date +%F).sql
加-B恢复数据时不需要重新创建数据库。
压缩备份：
Mysql –uroot –p ‘password’ –B oldboy |gzip </backup/$(date +%F).sql.gz
指定字符集备份：
Mysqldump–u root –p ‘password’ –default-character-set=gbk –B oldboy |gzip </backup/$(date+%F).sql.gz
备份多个数据库：
Mysqldump–u root –p ‘password’ –B mysql oldboy |gzip </backup/$(date +%F).sql.gz
分库备份：
Mysqldump–u root –p ‘password’ –B mysql |gzip </backup/mysql_$(date +%F).sql.gz
Mysqldump–u root –p ‘password’ –B oldboy |gzip </backup/oldboy_$(date +%F).sql.gz
备份单个表：
Mysql –uroot –p ‘password’ mysql mysql-table|gzip </backup/mysql_$(date +%F).sql.gz
备份多个表：
mysql –uroot –p ‘password’ mysql 表名1 表名字2 |gzip </backup/mysql_$(date+%F).sql.gz
分表备份：
mysql –uroot –p ‘password’ mysql 表1 |gzip </backup/表1_$(date +%F).sql.gz
mysql –uroot –p ‘password’ mysql 表2 |gzip </backup/表2_$(date +%F).sql.gz
备份数据库的结构：
mysql –uroot –p ‘password’ –d mysql |gzip </backup/$(date+%F).sql.gz
gzip –dxxxxx.sql.gz 解压文件（注意，用gzip –d解压时会把自动源文件删掉）
-
2. ll –lrt 命令解释：让同名字的排列在一起显示。
mysql –uroot –p ‘password’ –compact mysql |gzip </backup/$(date +%F).sql.gz
compact：过滤一些没有用的注释。测试时比较常用。
恢复数据库
>Source备份数据库名
>Sourcebackup.sql
MySQL主从复制
master端脚本执行时，在flush read with lock 不能执行操作，此过程需要跳出mysql窗口。
mysql -u root-p'mysqlpassword' <<EOF
flush tables with readlock;
system mysql -u root-p'mysqlpassword' -e "show master status" >/tmp/mysql.log
system mysqldump -u root-p'mysqlpassword' -B darren > /tmp/$(date +%F).sql
EOF
发邮件提醒：
slave stop SQL_THREAD
MySQL从库记录binlog从库做为备份服务器时开启
log-slave-update
log-bin=mysql-bin
expire_logs_days=7 <==find /data/ -typef –name “mysql-bin.000*” –mtime +7 | xargs rm –f
忽略库同步参数
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
replicate-ignore-db=mysql 主从库都配置这条才可以做到主从不同步mysql库
binlog-do-db=xxx 同步指定的库
replicate-do-db=xxx同步指定的库。
主从复制不同的错误模拟：
当从库中出现错误error的提示是sql线程为NO时只需
-
1.stop slave
-
2.set global sql_slave_skip_counter =1
-
3.start slave
即可恢复同步状态。
根据错误号跳过指定错误：
slave-skip-errors = 1032,1062
vi /usr/local/my.cnf 在配置文件中修改。
也可以在启动数据库的时候添加—slave-skip-errors=all
更改mysql数据库默认引擎
alter table student engine=myisam;
alter table student engine innodb;
主从同步，互为主从：
vi my.cnf
auto_increment_increment=2
auto_increment_offset=1
vi my.cnf
auto_increment_increment=2
auto_increment_offset=2
-----------------------------------------------------------
find /xx -type f ! -name "file10" -exec rm -f {}\;
find /xx -type f ! -name "file10"|xargs rm-f 删除指定目录下的所有文件，保留一个指定文件
---------------------------------------------------------------
find /tmp -mtime +7 -type f -name *.sh[ab] -exec rm -f {}\;
假如在一个目录中保留最近7天的文件，7天前的文件自动删除
###################################################################################
#!/bin/sh
for dbname in oldboy1 oldboy2 oldboy3
do
mysqldump -u root -p'password' -F -B $dbname > /tmp/mysqlbackup/$dbname_$(date +%F).sql
done
#sh -X test.sh 大x的意思是执行的时候显示执行过程，可用以脚本排错。
#去库名
#
#!/bin/sh
for dbname in `mysql -u root -p'mysqlpassword' -e"show databases;" | sed '1,2d'`
do
mysqldump -u root -p'password' -F -B $dbname > /tmp/mysqlbackup/$dbname_$(date +%F).sql
###################################################
3306
3307
3308
3309
把回车变成空格
tr "\n" " "
3306 3307 3308 3309
####################################################
主从同步备份：
选择在从库上做备份
开启binlog功能
执行slave stop SQL_THREAD;
然后备份mysqldump -u root -p'password' -B -F wordpress > /backup/$(date+%F).sql
增量备份就是全备之后的binglog日志。
执行slave start SQL_THREAD;
主从同步的情况下，从库备份搞定！
#############################################################################
模拟错误删除数据库的解决办法：
首先确保数据库有全备份（全备之后要刷新binlog）。增量备份也要有。
然后网站出现故障。
这时要刷新binlog目的是记录现在刷新的binlog到上次刷新的binlog的内容，这段内容就是出现故障的关键。
mysqladmin -u root -p"password" flush-logs
cp -r /data/mysql-bin.0003 /backup/binlog/backup/
mysqlbinlog mysql-bin.00003 >bin.sql
查看binlog找到其中的错误内容，然后删掉。
把全备恢复到数据库，然后再把增量binlog恢复。
##################################################################
一主多从宕机恢复步骤：
登录从库show processlist查看更新状态。
选择pos最大的为主库。
mysql-bin001508
3023435
10.1.1.22
rep1
3306
然后在每个从库上执行stop slave io_thread;
show processlist;
直到看到has read all relay log;表示从库更新都执行完毕。
在10.1.1.22上编辑：vi /etc/my.cnf
log-bin=mysqlbin #开启binlog注释掉log-slave-updatesread-only等
重启数据库10.1.1.22：/etc/init.d/mysqld resart
stop slave
reset master
quit;
进入到数据库目录 删掉master-info relay-log.info
重启数据库10.1.1.22: /etc/init.d/mysqld restart
########################################################
登录所有从库
stop slave；
change master to master_host='10.1.1.22';//如果不同步就指定起始点。
start slave
show slave status\G
平时访问数据库需要用域名就需要更改解析。
############################################################
所有数据库中的所有表加锁。在整体转储过程中通过全局读锁定来实现。该选项自动关闭
数据库引擎为innodb时候备份时加上--single-transaction
数据库引擎为myisam时候备份时加上--lock-tables。
-d 参数为指定库
以时间为点处理
mysqlbinlog mysqlbin0001 --start-datetime=‘’--stop-datetime=‘’mysql-bin0009 -r time.sql
以位置为点处理
mysqlbinlog mysqlbin0001 --strt-position=753
#######################################################################################
#!/bin/sh
mkdir -p /tmp/backup/$(date +%F)
for dbname in `mysql -u root -p'mysqlpassword' -e"show databases;" | sed '1,2d'`
do
mysqldump -u root -p'password' -F -B $dbname > /tmp/mysqlbackup/$(date +%F)/$dbname.sql
done
rsync -az /data/mysql-bin.00* /tmp/backup/$(date +%F)/
恢复：
#!/bin/sh
mysql -u root -p'password' < /tmp/mysqlbackup/$(date+%F)/$dbname.sql
#######################################################################
建表：
create table student(
id int(4) not null primary key auto_increment,
name char(20) not null
);
---------------------------
int序号 （4）4行
char内容（20）最长20个字符
---------------------------
create table students(
sno int(10) not null comment '学号',
sname varchar(16) not null comment '姓名'
);
-----------------------------------------------
创建索引：
create table test(
id int(4) not null primary key auto_increment,
name char(20) not null,
key `索引名`(`name`)
);
------------------------------------------------
primary key主键的意思是标识唯一，比如学生的学号。
increment 自增长。
sesc student 查看表结构。
show create table student \G 查看表的基本要素。
show index from students 查看索引，后面也可跟\G
alter table student add index index_name（name）;添加索引；为name添加索引。
alter table student drop index index_name;删除索引
alter table student add primary key（ID）;
alter table student drop primary key;
----------------------------------------------------
插入数据：
insert into student(id,name) values(001,'wangjia1');
insert into student(id,name)values(001,'wangjia1'),(002,'wangjia2');
查询数据：
select * from student;
select * from student limit 2;
select * from student limit 0,2;
select * from student order by id limit 0,3;
select * from student where id=6;
select * from student where name='wangjia3';
select * from student where id=7 and name='wangjia';
select * from student where id=7 or name='wangjia';
select * from student where id<7 and id>3;
更新数据：
update student set name='oldboy' where id=1;
删除数据
delete from student where id=100;
在表中增删改字段
alter table student add sex char(4);
later table student add age char(4) after name;
更改表名：
rename table student to teach;
mysql -u root -p'mysqlpassword' <<EOF
flush tables with read lock;
system mysql -u root -p'mysqlpassword' -e "show masterstatus" >/tmp/mysql.log
system mysqldump -u root -p'mysqlpassword' -B darren >/tmp/$(date +%F).sql
EOF
本文转自 王家东哥 51CTO博客，原文链接:http://blog.51cto.com/xiaodongge/1901343