mysql运维思想 mysql运维思想 MySQL主从复制

1.      授权开发人员mysql权限。

主库：

从库：

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恢复数据时不需要重新创建数据库。

压缩备份：

指定字符集备份：

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’ –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

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

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的意思是执行的时候显示执行过程，可用以脚本排错。

#去库名

#

for dbname in `mysql -u root -p'mysqlpassword' -e"show databases;" | sed '1,2d'`

###################################################

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

然后在每个从库上执行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 

#######################################################################################

mkdir -p /tmp/backup/$(date +%F)

mysqldump -u root -p'password' -F -B $dbname  > /tmp/mysqlbackup/$(date +%F)/$dbname.sql

rsync -az /data/mysql-bin.00* /tmp/backup/$(date +%F)/

恢复：

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(

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

 本文转自 王家东哥 51CTO博客，原文链接:http://blog.51cto.com/xiaodongge/1901343