MySQL 使用者管理備份複制恢複

常用檢視指令：

mysql> select version();

<a></a>

mysql> status;

mysql> show variables\G;

mysql> show global variables;

mysql> show processlist\G;

使用者管理權限：

建立使用者：

mysql> use mysql

mysql> create user linna;

Query OK, 0 rows affected (0.04 sec)

mysql> select Host,User,Password from user;

mysql> create user sandy identified by '123456';

Query OK, 0 rows affected (0.00 sec)

為使用者設定密碼：

mysql> set password for linna@'%' =password('123456');

授予權限：

mysql> grant create on *.* to linna@'%';

mysql> grant select on *.* to linna@'%';

Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.02 sec)

顯示賦予使用者的權限：

mysql> show grants for linna@'%'\G;

mysql> grant select on mydb.stu to kate@'192.168.0.%' identified by 'redhat';

Query OK, 0 rows affected (0.05 sec)

mysql> drop user kate@'192.168.0.%';

收回權限：

mysql> revoke select on *.* from linna@'%';

将某個字段的查詢權限授權使用者：

mysql> grant select(course) on mydb.course to sandy@'%';

允許sandy将權限授予其他人：

mysql> grant select on mydb.course to sandy@'%' with grant option;

将表中的所有權限授予使用者：

mysql> grant all privileges on db.* to kate@'%' identified by 'redhat';

PS：super privilege權限終止用戶端連接配接，并能改變全局變量。

限定資源使用數：

mysql> grant select on *.* to 'linna'@'%' with max_queries_per_hour 5;

with_queries_per_hour 每小時發起的最大查詢數

max_update_per_hour 每小時最大的修改數

max_connections_per_hour 同一個使用者每小時内的最多連接配接數

max_user_connections 同一個使用者在同一時刻内的最大并發連接配接數

mysql> select current_user(); //** 登入時比對到的使用者賬号（授權資訊）

mysql> select user(); //**登入時真正的使用者身份

mysql> show grants; //**檢視自己所擁有的權限

日志備份和恢複

日志種類：4+1種日志

the error log 錯誤日志

mqld在啟動或者關閉過程中所産生的資訊；

重要的關鍵的錯誤資訊；

排程事件的執行過程；

如果啟用主從架構，從伺服器的說明資訊。

啟動錯誤日志：mysqld_safe --log-error=/var/log/mysqld.err 如果不指路徑，預設存放在資料目錄下host_name.err。

--log_warnings 1（預設值1，啟用）是否将警告資訊記錄到錯誤日志中。

大于1，一些非法終止資訊也會被記錄到錯誤日志中。

PS：如果沒有定義錯誤日志錯誤資訊将被定義到标準錯誤輸出中去。

the binary log 二進制日志

對資料本身産生修改（或者可能發生修改）的動作将被記錄下來。

作用：實作point-in-time-recovery（時間點恢複）的功能；實作複制功能。

存放位置：記錄在資料目錄下mysql-bin.index(0000001)

啟用二進制日志：--log-bin = /data/logs/binary/changelog

--log-bin-index = /data/logs/relay/binarylog.index

檢視二進制日志：mysqlbinlog

[root@server27 data]# /usr/local/mysql/bin/mysqlbinlog mysql-bin.000001

mysqlbinlog mysql-bin.000001 \

--start-datetime="2011-03-05 17:34:40" \

--end-datetime="2011-03-05 17:34:56" > sixteensecs.sql

mysqlbinlog --read-from-remote-server -uuser -p \ //**檢視遠端伺服器上的二進制日志

mysql-bin.000001 -h 192.168.0.182 -P 3306 \

--start-position=932 --end-position=1132 > remote000001.sql

在MySQL中檢視：

mysql> show binlog events in 'mysql-bin.000001' from 7548 limit 3\G;

the general query log 一般查詢日志

the slow query log 慢速查詢日志

特殊日志：relay log 中繼日志主從複制模式中在從伺服器上使用到的一種日志。

預設情況下日志存放在資料目錄下，不建議将資料與二進制檔案放在一起。

日志滾動：

flush logs 手動的方式滾動錯誤日志和二進制日志

purge binary logs 删除指定位置之前所有的日志

mysql> PURGE BINARY LOGS TO ’mysql-bin.00007’;

mysql> PURGE BINARY LOGS BEFORE ’2011-3-5 23:00:00’;

日志錯誤檢查，修複：

[root@server27 mydb]# myisamchk --extend-check course2.MYI

Checking MyISAM file: course2.MYI

Data records: 0 Deleted blocks: 0

- check file-size

- check record delete-chain

- check key delete-chain

- check index reference

- check records and index references

[root@server27 mydb]# myisamchk --recover course2.MYI

- recovering (with keycache) MyISAM-table 'course2.MYI'

Data records: 0

在MySQL指令行下進行檢查：

mysql> use mydb

Database changed

mysql> check tables course2;

mysql> repair table course2;

對表本身作分析和優化：

mysql> analyze table course2;

mysql> optimize table course2;

對MySQL備份和恢複：

備份方式：完全備份增量備份差異備份

冷備溫備（隻讀不能寫）熱備

邏輯備份：備份資料本身内容

實體備份：備份資料檔案

一緻性備份：所有的備份檔案的最後修改時間都在備份時間點之前，而且所有日志中的資料都已經同步到硬碟中去。可以做恢複。

非一緻性備份：備份檔案的最後修改時間可能在備份時間點之後。

常用備份工具：

Mysqldump 邏輯備份溫備

Provides a logical backup of entire database servers, individual databases, individual tables, or even subsets of data using the --where option

The logical backup created using mysqldump is often called a data dump

The output is in ASCII format — easily readable and manipulated

備份：

[root@server27 data]# mysqldump -uroot -p mydb > /root/mysql.sql

[root@server27 data]# mysqldump -uroot -p --events --routines --triggers --databases mydb mysql > /root/mydb-`date +"%F"`.sql

恢複：

PS：恢複前要先建立mydb資料庫。

[root@server27 data]# mysql -uroot -p mydb < /root/mysql.sql

相關參數：

PS：參考葉金榮《MySQL備份和恢複》。

SELECT INTO OUTFILE

實作隻針對某一個表或者表中的資料進行備份。

mysql> select * into outfile '/tmp/stu.sql' from stu;

恢複，先建立表：

使用場景：truncate table stu;

mysql> load data infile '/tmp/stu.sql' into table stu;

mk-parallel-dump --basedir=/backups

mk-parallel-restore /backups

File system snapshot

通過LVM快照了備份和恢複

[root@server27 ~]# pvcreate /dev/sda{5,6}

[root@server27 ~]# vgcreate vg1 /dev/sda{5,6}

[root@server27 ~]# lvcreate -L 2G -n mydata vg1

[root@server27 ~]# mkfs.ext3 /dev/vg1/mydata

[root@server27 ~]# mkdir /mydata

[root@server27 ~]# mount /dev/vg1/mydata /mydata

[root@server27 ~]# cp -rp /data/* /mydata/

[root@server27 mydata]# chown mysql:mysql /mydata/

編輯mysql的主配置檔案将資料目錄改為mydata。

mysql> flush tables with read lock;

mysql> flush logs;

[root@server27 mydata]# lvcreate -L 20M -s -n mysnap /dev/vg1/mydata

[root@server27 mydata]# mkdir /mysnap

[root@server27 mydata]# mount /dev/vg1/mysnap /mysnap/

假設要備份mydb資料庫

[root@server27 mysnap]# cp -rp mydb /tmp/mydb2

[root@server27 /]# umount /mysnap/

[root@server27 /]# lvremove /dev/vg1/mysnap

[root@server27 /]# cp -rp /tmp/mydb2 /mydata/mydb

Mysqlhotcopy

yum install perl-DBI -y

yum install perl-DBD-MySQL -y

mysqlhotcopy mydb /backup

mysqlhotcopy --method=scp mydb \ [email protected]:/backup

時間點恢複（PITR）

mysqldumo+binlog 做時間點恢複

[root@server27 data]# mysqldump -uroot --flush-logs --lock-all-tables --master-data=2 --events --routines --triggers mydb >/root/mydb-`date +"%F-%H-%M-%S"`.sql

然後修改資料庫：

将mydb資料庫删除，然後嘗試恢複：

mysql> create database mydb;

[root@server27 data]# mysql -uroot -p mydb < /root/mydb-2011-04-10-18-23-57.sql

看，沒有備份後新添加的表t1，如何恢複t1?這就要基于時間點來恢複了！

mysql> show binary logs;

mysql> show binlog events in 'mysql-bin.000002'\G;

做時間點恢複：

[root@server27 data]# mysqlbinlog /data/mysql-bin.000002 --start-position 6355 --stop-position 7701 > /tmp/mydbrestore.sql

[root@server27 data]# mysql -uroot -p &lt; /tmp/mydbrestore.sql

PS：xtrabackup 免費開源備份工具，企業中使用。

本文轉自 490999122 51CTO部落格，原文連結：http://blog.51cto.com/lyp0909/561847，如需轉載請自行聯系原作者

MySQL 使用者管理備份複制恢複

繼續閱讀

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述

MySQL 使用者管理 備份複制 恢複

繼續閱讀

MySQL 使用者管理備份複制恢複