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