mysql运维思想

mysql运维思想

 

 

 

  1. 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. 1.     生产环境读写分离账户设置:

主库(提供写服务):webmysql(数据库)  user(账户)  ip10.1.1.21 port  3306

从库(提供读服务):webmysql(数据库)  user(账户)  ip10.1.1.22 port  3306

  1. 2.     数据库备份。

从库备份要打开binlog,备份包括全备和binlog增量备份。

  1. 3.     创建数据库指定字符集:

GBK:create database darren DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
UTF8: CREATE DATABASE darren DEFAULT CHARACTER SET utf8 COLLATEutf8_general_ci;

  1. 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数据库的字符集类型。

Show characterset;  查看所有的字符集。

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  不重启数据使修改数据库参数,让其生效。如果想重启还生效需要改配置参数。

key_buffer_size=16M

远程连接数据库:

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 表名;

  1. 5.     刷新mysql系统权限

flushprivileges;

showcreate databases oldboy \G;

  1. 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 表名表名字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解压时会把自动源文件删掉)

  1. 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. 1.stop slave

  2. 2.set global sql_slave_skip_counter =1

  3. 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



上一篇:阿里云双11拼团价低至冰点,领1亿元双11补贴


下一篇:云计算与钉钉相结合——钉钉成功案例分析