MySQL日常維護（超級大雜燴）

MySQL 日常維護

其中第一至第六, 每天都需要執行, 第七到第九定期執行(1周或者2周一次？)

一檢查資料庫運作狀态

1 通過putty.exe登入遠端資料庫伺服器,在shell指令行裡面執行:

service mysql status

SUCCESS! MySQL running (1302)

則表示資料庫正在運作之中

2 檢查錯誤日志

進入/var/lib/mysql,檢視錯誤日志，例如mysql.err。看近期mysql在運作中是否有錯誤異常發生。

tail -f /var/lib/mysql/mysql.err

比如發現類似錯誤：

090418 19:59:11 [ERROR] Got error 127 when reading table ‘./csf/ms_company’

對于127以及126的類似錯誤，可以采取如下解決措施：

(1) 進入mysql視窗執行： Mysql> repair csf.ms_company;

(2) 進入shell指令視窗： Shell> myisamchk /usr/local/mysql/var/csf/ms_company.MYI

【】線上修複的時候，要保證沒有程式在通路要修複的表。

3 檢查備份日志

檢查前一天的備份的日志，看備份是否成功完成，或者備份過程中出

現什麼錯誤提示。

4 檢查資料庫排程任務events事件

二檢查資料庫伺服器磁盤空間, 記錄最近一天磁盤使用量

通過如下shell指令檢查：

df -hvT

Filesystem Type Size Used Avail Use% Mounted on

devtmpfs devtmpfs 852M 0 852M 0% /dev

tmpfs tmpfs 872M 0 872M 0% /dev/shm

tmpfs tmpfs 872M 8.6M 863M 1% /run

tmpfs tmpfs 872M 0 872M 0% /sys/fs/cgroup

/dev/mapper/ol-root xfs 14G 3.6G 9.9G 27% /

/dev/sda1 xfs 1014M 174M 841M 18% /boot

tmpfs tmpfs 175M 0 175M 0% /run/user/0

得出目前已剩餘磁盤空間,跟昨天的記錄一對比,得出每天磁盤使用量為3G，如果磁盤可用空間小,就需要清理舊的binlog.

三檢查表

打開MySQL Administration工具，登入資料庫伺服器，檢查每個庫的表情況，

遇到有壞表的，直接Repair Table修複掉。

四檢查master伺服器的master服務以及slave機的slave服務狀态

檢視master伺服器狀态：行正常的狀态如下所示:

mysql -u root -p

show master status;

+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------------+

1fdb51e5-6242-11e8-9e40-000c293b8095:1-29808 |

1 row in set (0.00 sec)

檢視mysql使用者線程狀态：運作正常的狀态如下所示:

show processlist;

+----+------+--------------------+------+------------------+------+-----------------------------------------------------------------------+------------------+

5 rows in set (0.00 sec)

檢視slave伺服器狀态：運作正常的狀态如下所示:

show slave status \G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.11

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000023

Read_Master_Log_Pos: 231

Relay_Log_File: mysqlstandby-relay-bin.000008

Relay_Log_Pos: 361

Relay_Master_Log_File: mysql-bin.000023

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 231

Relay_Log_Space: 541

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: 1fdb51e5-6242-11e8-9e40-000c293b8095

Master_Info_File: /var/lib/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set: 08a5acd3-6673-11e8-b993-000c29ee89cd:1-4,

1fdb51e5-6242-11e8-9e40-000c293b8095:1-29808

Auto_Position: 1

五檢查Excel錯誤

詳細見文檔“excel_error_check.sql”。

六， MySQL使用者賬戶管理

對使用者嚴格管理，單使用者隻能賦予執行單個業務的權限，防止對資料的誤操作。示範指令如下：

建立使用者：

grant select,insert,update,delete on sbtest.* to

'test'@'192.168.1.10'

identified by 'jdy62ZHA';

檢視使用者:

select * from mysql.user;

select * from mysql.db;

select * from mysql.tables_priv;

select * from mysql.columns_priv;

select * from mysql.procs_priv;

賦予權限

grant update on sbtest.* to

;

grant update(id) on sbtest.sbtest to

收回權限

revoke drop on *.* from

删除過期使用者

drop user

七，定期索引檢查、索引優化

重點檢查大表比如rpt_style1_0到rpt_style1_f等的索引。

八，定期檢查表結構以及資料

通過 Administration工具檢查，将主機與從機的庫結構以及資料進行

全方位的對比對比。比如說,表以及資料量的總記錄,索引的記錄數,觸發器的記錄數,存儲函數存儲過程的記錄數等.

1 檢查和修複MyISAM表的一個方式是使用CHECK TABLE和REPAIR TABLE語句。

2 優化MyISAM表，對于沒有問題的表執行optimize table進行做碎片整理，回收空閑空間，例如:

optimize table sbtest.sbtest;

+---------------+----------+----------+-------------------------------------------------------------------+

2 rows in set (1 min 25.34 sec)

或者是，如果可以停一會mysqld伺服器，進入資料庫目錄，當mysqld停止時候使用該指令：

service mysql stop

cd /var/lib/mysql

myisamchk -r -s -sort-index -o --myisam_sort_buffer_size=16M */*.MYI

myisamchk: error: 140 when opening MyISAM-table 'mysql/columns_priv.MYI'

myisamchk: error: 140 when opening MyISAM-table 'mysql/db.MYI'

myisamchk: error: 140 when opening MyISAM-table 'mysql/event.MYI'

myisamchk: error: 140 when opening MyISAM-table 'mysql/func.MYI'

myisamchk: error: 140 when opening MyISAM-table 'mysql/help_category.MYI'

myisamchk: error: 140 when opening MyISAM-table 'mysql/help_keyword.MYI'

myisamchk: error: 140 when opening MyISAM-table 'mysql/help_relation.MYI'

myisamchk: error: 140 when opening MyISAM-table 'mysql/help_topic.MYI'

myisamchk: error: 140 when opening MyISAM-table 'mysql/ndb_binlog_index.MYI'

myisamchk: error: 140 when opening MyISAM-table 'mysql/plugin.MYI'

myisamchk: error: 140 when opening MyISAM-table 'mysql/proc.MYI'

myisamchk: error: 140 when opening MyISAM-table 'mysql/procs_priv.MYI'

myisamchk: error: 140 when opening MyISAM-table 'mysql/proxies_priv.MYI'

myisamchk: error: 140 when opening MyISAM-table 'mysql/servers.MYI'

myisamchk: error: 140 when opening MyISAM-table 'mysql/tables_priv.MYI'

myisamchk: error: 140 when opening MyISAM-table 'mysql/time_zone_leap_second.MYI'

myisamchk: error: 140 when opening MyISAM-table 'mysql/time_zone.MYI'

myisamchk: error: 140 when opening MyISAM-table 'mysql/time_zone_name.MYI'

myisamchk: error: 140 when opening MyISAM-table 'mysql/time_zone_transition.MYI'

myisamchk: error: 140 when opening MyISAM-table 'mysql/time_zone_transition_type.MYI'

myisamchk: error: 140 when opening MyISAM-table 'mysql/user.MYI'

3 修複innodb表

mysql目前沒有修複innodb 表的工具，隻能用innodb_force_recovery=1，避免在導出資料時再crash。在my.cnf中設定好後重新開機庫，再用 mysqldump或者select *把出問題的表導出來。然後重新導入（删除原表）。

九，檢查備份檔案，将從機的備份資料恢複到測試庫，檢驗備份資料是否正确。

具體操作參見文檔“MySQL備份恢複初稿.docx”

MySQL運維之--日常維護操作

一、Linux核心和發行版本

uname -a

Linux mysql 4.1.12-94.3.9.el7uek.x86_64 #2 SMP Fri Jul 14 20:09:40 PDT 2017 x86_64 x86_64 x86_64 GNU/Linux

cat /etc/issue

Kernel \r on an \m

二、glibc的版本

/lib/libc.so.6 ---沒有man函資料的動态連結庫

三、MySQL的版本

MySQL二進制分發版的檔案名格式為：mysql-VERSION-OS.tar.gz

例如：Linux-Generic(glibc 2.5) (x86,64bit),Compressed TAR Archive(mysql-5.6.16-linux-glibc2.5-x86_64.tar.gz)

1、下載下傳

https://dev.mysql.com/downloads/mysql/

2、查rpm包裝在什麼目錄下

rpm -qpl MySQL-server-5.6.40-1.el7.x86_64.rpm |more

warning: MySQL-server-5.6.40-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

/etc/init.d/mysql

/etc/logrotate.d/mysql

/etc/my.cnf

/etc/my.cnf.d

/usr/bin/innochecksum

/usr/bin/my_print_defaults

/usr/bin/myisam_ftdump

/usr/bin/myisamchk

/usr/bin/myisamlog

/usr/bin/myisampack

/usr/bin/mysql_convert_table_format

/usr/bin/mysql_fix_extensions

/usr/bin/mysql_install_db

/usr/bin/mysql_plugin

/usr/bin/mysql_secure_installation

/usr/bin/mysql_setpermission

/usr/bin/mysql_tzinfo_to_sql

/usr/bin/mysql_upgrade

/usr/bin/mysql_zap

/usr/bin/mysqlbug

/usr/bin/mysqld_multi

/usr/bin/mysqld_safe

/usr/bin/mysqldumpslow

/usr/bin/mysqlhotcopy

/usr/bin/mysqltest

/usr/bin/perror

/usr/bin/replace

/usr/bin/resolve_stack_dump

/usr/bin/resolveip

/usr/lib64/mysql/plugin/adt_null.so

/usr/lib64/mysql/plugin/auth.so

/usr/lib64/mysql/plugin/auth_socket.so

/usr/lib64/mysql/plugin/auth_test_plugin.so

/usr/lib64/mysql/plugin/connection_control.so

/usr/lib64/mysql/plugin/daemon_example.ini

/usr/lib64/mysql/plugin/debug/adt_null.so

/usr/lib64/mysql/plugin/debug/auth.so

/usr/lib64/mysql/plugin/debug/auth_socket.so

/usr/lib64/mysql/plugin/debug/auth_test_plugin.so

/usr/lib64/mysql/plugin/debug/connection_control.so

/usr/lib64/mysql/plugin/debug/innodb_engine.so

rpm -qpl MySQL-client-5.6.40-1.el7.x86_64.rpm |more

warning: MySQL-client-5.6.40-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

/usr/bin/msql2mysql

/usr/bin/mysql

/usr/bin/mysql_config_editor

/usr/bin/mysql_find_rows

/usr/bin/mysql_waitpid

/usr/bin/mysqlaccess

/usr/bin/mysqlaccess.conf

/usr/bin/mysqladmin

/usr/bin/mysqlbinlog

/usr/bin/mysqlcheck

/usr/bin/mysqldump

/usr/bin/mysqlimport

/usr/bin/mysqlshow

/usr/bin/mysqlslap

/usr/share/doc/MySQL-client-5.6.40

/usr/share/doc/MySQL-client-5.6.40/COPYING

/usr/share/doc/MySQL-client-5.6.40/README

/usr/share/man/man1/msql2mysql.1.gz

/usr/share/man/man1/mysql.1.gz

/usr/share/man/man1/mysql_config_editor.1.gz

/usr/share/man/man1/mysql_find_rows.1.gz

/usr/share/man/man1/mysql_waitpid.1.gz

/usr/share/man/man1/mysqlaccess.1.gz

/usr/share/man/man1/mysqladmin.1.gz

/usr/share/man/man1/mysqlbinlog.1.gz

/usr/share/man/man1/mysqlcheck.1.gz

/usr/share/man/man1/mysqldump.1.gz

/usr/share/man/man1/mysqlimport.1.gz

/usr/share/man/man1/mysqlshow.1.gz

/usr/share/man/man1/mysqlslap.1.gz

3、更改rpm安裝路徑

rpm --help

rpm --prefix --relocate

rpmbuild spec binary rpm

yum install

四、rpm安裝

rpm -ivh xxx.rpm

rpm -pql xxx.rpm

MySQL 執行個體安裝和啟動

1. 安裝

mysql_install_db --defaults-file=/root/data/mysql3306/my.cnf --basedir=/usr/ --datadir=/root/data/mysql3306/data

2. 啟動

mysqld_safe --defaults-file=/root/data/mysql3306/my.cnf &

3. 登入

mysql -h 127.0.0.1 -u root -P 3306 -p

五、安裝示範：

1.關閉mysql

ps -ef|grep mysqld

kill 3397 3801

2.安裝

cat init3306.sh

sh init3306.sh

3.啟動

cat start3306.sh

sh start3306.sh

注意：mysql_install_db(通過安裝rpm包産生mysql_install_db)，有如下指令檢視：

rpm -pql MySQL-server-5.6.40-1.el7.x86_64.rpm |grep install

/usr/share/man/man1/mysql_install_db.1.gz

/usr/share/man/man1/mysql_secure_installation.1.gz

which mysql_install_db

4.看日志

tail -100f /var/lib/mysql/mysql.err

發生資料字典不存在，就會自動建立。。。。

5.檢視程序

root 1948 1 0 19:17 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/mysql.pid

mysql 2129 1948 0 19:17 pts/0 00:00:01 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=mysql.err --pid-file=/var/lib/mysql/mysql.pid

root 2328 1539 0 22:12 pts/0 00:00:00 grep --color=auto mysqld

mysqld_safe是mysqld的父程序

6.登入

sh my3306.sh --mysql -h 127.0.0.1 -u root -P 3306

六、腳本：

1.---安裝mysql:

vi init3306.sh

rm -rf /root/data/mysql3306/data/*

rm -rf /root/log/mysql3306/iblog/*

rm -rf /root/log/mysql3306/binlog/*

chmod -R 777 /root/data/mysql3306/data/

chmod -R 777 /root/log/mysql3306/iblog/

chmod -R 777 /root/log/mysql3306/binlog/

chmod 755 /root/data/mysql3306/my.cnf

mysql_install_db --defaults-file=/root/data/mysql3306/my.cnf --basedir=/usr/ --datadir=/root/data/mysql3306/data

chmod -R 777 /root/data/mysql/3306/data/

2.--啟動mysql:

vi start336.sh

mysqld_safe --defaults-file=/root/data/mysql3306/my.cnf &

3.--登入mysql:

vi my3306.sh

mysql -h127.0.0.1 -uroot -P3306 -p

------研究下

cd /root/data/mysql3306/data/mysql

cd /root/data/mysql3306/data/performance_schema --性能相關的

cd /root/data/mysql3306/data/test ---測試庫

cd /root/log/mysql3306/iblog/ ---innodb自己的資料和日志

------了解my.cnf(多執行個體用端口來取分)

vi /root/data/mysql3306/my.cnf

[client]

port=3306

socket=/root/data/mysql3306/run/mysql.sock

[mysql]

promprt=\\u@\\d

\\r:\\m:\\s

[mysqld]

default-storage-engine=INNODB

character-set-server=iatin1

explicit_defaults_for_timestamp=true

#dir

innodb_log_group_home_dir=/root/log/mysql3306/iblog

innodb_data_home_dir=/root/log/mysql3306/iblog

basedir=/usr

datadir=/root/data/mysql3306/data

tmpdir=/root/data/mysql3306/tmp

slave_load_tmpdir=/root/data/mysql3306/tmp

log-error=/root/data/mysql3306/log/alert.log

slow_query_log_file=/root/data/mysql3306/log/slow.log

relay_log_info_file=/root/log/mysql3306/binlog/relay-log.info

master-info-file=/root/log/mysql3306/binlog/master.info

log-bin=/root/log/mysql3306/binlog/binlog

relay-log=/root/log/mysql3306/binlog/relaylog

innodb_force_recovery=0

七、操作mysql

1、登入mysql:

本地：

遠端：

mysql -u root -p -h 192.168.1.12

多執行個體：

mysql -u root -p -P 3306

2、使用者操用

(1)建立使用者

create user

'test2'@'192.168.1.10'

identified by 'test2';

(2)使用者授權

單純的授權

grant all privileges on *.* to

'test2'@'localhost'

grant insert,update,delete,select on *.* to

授權并建立使用者

--建立使用者并刷緩存

--對象權限

--系統權限 (super相當于oracle中的dba權限)

grant super on *.* to

3、實操

--檢視所有的資料庫

show databases;

--切到mysql資料庫

use mysql;

----查mysql的所有使用者，這個是由mysql_install_db建立的

select user,host,password from mysql.user;

--all代表(select update,delete,alter admin,super_acl),第一個*代表所有資料庫，第二個*代表所有對象，%代表所有的用戶端都可以通路。

'test2'@'%'

----用grant建立的使用者登入mysql

mysql -h 127.0.0.1 -u test2 -p

---目前是什麼使用者

select user();

---建立資料庫（mysql中的資料庫類似于oracle中的schema

create database test2;

---建立表

create table test2(id int) engine=innodb;

---sbtest.test2表的查詢授權給test2使用者

grant select on sbtest.test2 to

---把mysql.user表的使用者權限重新刷到記憶體中

flush privileges;

show master status \G

---檢視目前使用者的連接配接,線程形式（類似oracle中的v$session）

4、drop table處理

(可以快速切回來rename table test3 to test2;)

rename table test2 to test3;

備份mysqldump：

mysqldump -h 127.0.0.1 -u root -p sbtest test > /home/sbtest.test.sql

drop table sbtest;

5、自增主鍵(最好是自己定義主鍵，系統預設的是全局的增量)

create table test (id int primary key auto_increment,name varchar(100)) engine=innodb;

show create table test \G

create index test_name_idx on test(name);

insert into test(name) values('test');

select * from test;

6、alter table處理 --會動原來的資料，需要拷貝資料

alter table test add coll int;

7、執行計劃

select * from test where id=1 \G

explain select * from test where id=1;

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

create index test_id_coll_idx on test(id,coll);

+----+-------------+-------+-------+--------------------------+---------+---------+-------+------+-------+

create index test_col_name on test(coll,name);

explain select * from test where coll>10 and name='xx';

+----+-------------+-------+------+-----------------------------+---------------+---------+-------+------+------------------------------------+

alter table test drop index test_name_idx;

explain select * from test where coll>10 and name>'xx';

8、資料導出

（1）用dump導出資料

drop table sbtest.test;

use sbtest;

--導入資料

source /home/sbtest.test.sql

(2)用select導出資料

select * from test into outfile '/home/select_export_sbtest.test.sql';

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

9、資料遷移

(1)停機方式

mysqldump/loadata

(2)不停機方式

實體上：搭備庫（可以級聯5.5-->5.6，向下相容的）

把主庫read only,備庫就能把主庫轉過來的binlog消化完，再把備庫切為主

show global variables like '%read_only%';

+------------------+-------+

| Variable_name | Value |

| innodb_read_only | OFF |

| read_only | OFF |

| tx_read_only | OFF |

3 rows in set (0.00 sec)

set global read_only=on;

--插不進的，不能用root使用者

insert into test(name) values('xx');

(3)不同平台小表：oracle--->mysql

腳本：synfull.pl

(4)不同平台的一個大表遷多：增量遷移

a.把資料的全量遷過去

b.把遷的過程中産生的日志傳過去

c.apply增量

d.鎖表切切換

（5）增量

a.Oracle:物化視圖

b.MySQL:trigger

10、binlog

reset master; --會把目前的binlog清掉

show binlog events;

類似于：

mysqlbinlog -vvv /var/lib/mysql/mysql-bin.000024 > /home/mysql-bin.000024.txt

less /home/mysql-bin.000024.txt

WAL: write ahead log,日志優先寫

11、歸檔

flush logs;

write ahead log. recover backup, duriably. undo acid mvcc

12、參數和統計資訊

----參數

show variables;

show variables like '%bin%';

----統計資訊

show status;

show global status like '%insert%';

show variables like '%default%';

---不影響目前會話的操作，影響建立立的連接配接

set global default_storage_engine=myisam;

---影響目前會話的操作

set session default_storage_engine=myisam;

---連接配接池

show global variables like '%max_connect%';

+--------------------+-------+

| Variable_name | Value |

| max_connect_errors | 100 |

| max_connections | 151 |

2 rows in set (0.00 sec)

show global variables like '%timeout%';

+------------------------------+----------+

| Variable_name | Value |

| connect_timeout | 10 |

| delayed_insert_timeout | 300 |

| innodb_flush_log_at_timeout | 1 |

| innodb_lock_wait_timeout | 50 |

| innodb_rollback_on_timeout | OFF |

| interactive_timeout | 28800 |

| lock_wait_timeout | 31536000 |

| net_read_timeout | 30 |

| net_write_timeout | 60 |

| rpl_semi_sync_master_timeout | 10000 |

| rpl_stop_slave_timeout | 31536000 |

| slave_net_timeout | 3600 |

| wait_timeout | 28800 |

13 rows in set (0.00 sec)

show global variables like '%disconnect%';

+--------------------------------+-------+

| Variable_name | Value |

| disconnect_on_expired_password | ON |

MySQL資料庫的日常使用及維護

一：使用MySQL資料庫：

1、檢視資料庫結構：

檢視目前伺服器中有哪些庫：

SHOW DATABASES 語句：用于列出目前MySQL伺服器中包含的庫；經過初始化後的MySQL伺服器，預設建立了三個庫：information_schema、mysql和performance_schema（其中mysql庫中包含了使用者認證相關的表）

+--------------------+

| Database |

| information_schema |

| mysql |

| performance_schema |

檢視目前使用的庫中有哪些表：

SHOW TABLES 語句：用于列出目前所在的庫中包含的表。操作之前，需要先使用USE語句切換到所使用的庫：

show tables;

Mysql資料庫的資料檔案預設存放在/var/lib/mysql目錄下，每個資料庫對應一個子目錄，用于存儲資料表檔案；

每一個資料表對應為三個檔案，字尾名分别為”.frm”” .MYD ”” .MYI ”。

a.檢視表的結構：

DESCRIBE 語句：用于顯示表的結構，即組成表的各字段（列）的資訊；

需要指定“庫名.表名”作為參數；

若隻指定表名參數，則需先通過“USE”語句切換到目标庫。

建立及删除庫和表：

b、建立新的庫：

CREATE DATABASE 語句：用于建立一個新的庫，需指定資料庫名稱作為參數。

Eg：建立一個名為welcome的庫：

CREATE DATABASE welcome;

建立的空資料庫在/var/lib/mysql目錄下會自動生成一個與建立的庫名相同的空檔案夾。

c、建立新的表：

CREATE TABLE 語句：用于在目前庫中建立新的表，需指定資料表名稱作為參數，并定義該表格所使用的各字段。

格式： CREATE TABLE 表名（字段1名稱類型，字段2名稱類型，… ，PRIMARY kEY （主鍵名））

在welcome庫中建立users表。其中，字段定義部分的DEFAULT用于設定預設的密碼字串，PRIMARY用于設定主鍵字段名。

mysql> USE welcome;

mysql> CREATE TABLE users (user_name CHAR(16) NOT NULL, user_passwd CHAR(48) DEFAULT '', PRIMARY KEY (user_name));

删除一個資料表：

DROP TABLE 語句：用于删除庫中的表，需要指定“庫名.表名”作為參數；若隻指定表名參數，則需先通過“USE”語句切換到目标庫。

Eg：删除welcome庫中的users表：

mysql> DROP TABLE welcome.users;

删除一個資料庫：

DROP DATABASE 語句：用于删除指定的庫，需要指定庫名作為參數。

Eg：删除名為welcome的庫：

mysql> DROP DATABASE welcome;

3、管理表中的資料記錄：

插入資料記錄：

INSERT INTO 語句：用于向表中插入新的資料記錄。

格式： INSERT INTO 表名（字段1，字段2，…） VALUES（字段1的值，字段2的值，…）

向welcome庫中的users表中插入一條記錄：使用者www，對應的密碼為“123456”：

mysql> use welcome;

mysql> INSERT INTO users(user_name,user_passwd) VALUES('www',PASSWORD('123456'));

Eg：向welcome庫中的users表中插入一條新的記錄，使用者qqq，對應密碼“654321”。

mysql> INSERT INTO users VALUES('qqq',PASSWORD(654321));

查詢資料記錄：

SELECT 語句：用于從指定的表中查找符合條件的資料記錄。

格式： SELECT 字段名1，字段名2，… FROM 表名 WHERE 條件表達式

表示所有字段可以使用通配符“*”，若要列出所有的資料記錄則可以省略WHERE條件子句。

Eg：檢視welcome庫中的users表内的所有資料記錄：

mysql> select * from welcome.users;

根據特定的條件查找記錄，WHERE條件子句是必不可少的。

Eg：查找users表中使用者名為qqq的記錄，輸出其使用者名、密碼字段的資訊：

mysql> SELECT user_name,user_passwd from welcome.users where user_name='qqq';

修改資料記錄：

UPDATE 語句：用于修改、更新表中的資料記錄。

UPDATE 表名 SET 字段名1=字段值1[，字段名2=字段值2] WHERE 條件表達式

Eg：修改users表中使用者名為www的記錄，将密碼字串設為空值，并驗證：

mysql> UPDATE welcome.users SET user_passwd=PASSWORD('') WHERE user_name='www';

mysql資料庫伺服器中，用于通路資料庫的各種使用者資訊都儲存在mysql庫的user表中。

删除資料記錄：

DELETE 語句：用于删除表中指定的資料記錄。

格式： DELETE FROM 表名 WHERE 條件表達式

Eg：删除users表名為www的資料記錄，并驗證：

mysql> DELETE FROM welcome.users WHERE user_name='www';

mysql> SELECT * FROM welcome.users;

在mysql資料庫伺服器中，預設添加了從本機通路資料庫的空使用者（user、password均為空）。基于資料庫安全性考慮，應該删除這些空使用者。

mysql> SELECT user,host,password FROM mysql.user WHERE user='';

mysql> DELETE FROM mysql.user WHERE user='';

推薦閱讀：

Percona_Toolkit 簡介及安裝 -- MySQL管理者必備的工具包

http://www.linuxidc.com/Linux/2013-06/86631.htm

二：維護MySQL資料庫：

1、資料庫的使用者授權：

Mysql資料庫的root使用者賬号擁有對其所有庫、表的全部權限，頻繁使用root賬号會給資料庫伺服器帶來安全風險。是以實際工作中需要建立一些低權限的使用者：

授予權限：

GRANT 語句：專門用來設定資料庫使用者的通路權限。當指定的使用者名不存在時，GRANT語句将會建立新的使用者，否則用于修改已有使用者資訊。

GRANT 權限清單 ON 庫名.表名 TO 使用者名@來源位址 [ IDENTIFIED BY ‘密碼‘]

使用GRANT語句的注意事項：

權限清單：用于列出授權使用的各種資料庫操作，以逗号分隔。All表示所有權限；

庫名.表名：用于指定授權操作的庫和表的名稱，其中可以使用“*”。Eg：使用“welcome.*”表示授權操作的對象為welcome庫中的所有表。

使用者名@來源位址：用于指定使用者名稱和允許通路的客戶機位址，即誰能連接配接、能從哪裡連接配接。來源的位址可以是域名、IP位址，還可以使用“%”通配符，表示某個區域或網段内的所有位址。Eg：“%.benet.com”、“192.168.10.%”等

IDENTIFIED BY：設定使用者連接配接資料庫時所使用的密碼字元串。在建立使用者時，若省略則使用者的密碼将為空。

使用GRANT語句授權的使用者記錄，會儲存到mysql庫的user、db、host、tables_priv等。

Eg：添加一個名為zqq的資料庫使用者，并允許其從本機通路，對welcome庫中的所有表具有查詢權限，驗證密碼為“123456”。

mysql> GRANT select ON welcome.* TO

'zqq'@'localhost'

IDENTIFIED BY '123456';

Eg：以使用者zqq的身份連接配接到資料庫，當執行授權的資料庫操作時将被允許，而執行非授權的資料庫操作時将被拒絕：

在企業伺服器的應用中，資料庫與網站伺服器有時候是互相獨立的。是以在mysql伺服器中，應根據實際情況建立新的使用者授權，允許授權使用者從網站伺服器通路資料庫；

通常的做法是：建立一個或幾個網站專用的庫，并授權所有權限，限制通路的來源IP位址。

Eg：建立bash庫，并授權從IP位址為192.168.10.1的主機連接配接，使用者名為zqq，密碼123456，允許在bash庫中執行所有操作。

mysql> CREATE DATABASE bash;

mysql> GRANT all ON bash.* TO

'zqq'@'192.168.10.1'

檢視權限：

SHOW GRANTS 語句：專門用來檢視資料庫使用者的授權資訊，通過FOR語句可指定檢視的使用者對象（必須與授權時使用的格式： SHOW GRANTS FOR 使用者名@來源位址

Eg：檢視使用者zqq從主機192.168.10.1通路資料庫時得授權資訊。

mysql> SHOW GRANTS FOR

撤銷權限：

REVOKE 語句：用于撤銷指定使用者的資料庫權限，撤銷權限後的使用者仍然可以連接配接到mysql伺服器，但将禁止執行對應的資料庫操作：

格式：REVOKE 權限清單 ON 資料庫名.表名 FROM 使用者名@來源位址

Eg：撤銷使用者zqq從本機通路資料庫bash的所有權限：

mysql> REVOKE all ON bash.* FROM

；

二、資料庫的備份與恢複：

及時備份資料庫是資訊安全管理的重要内容之一，mysql資料庫的備份采用直接打包資料庫檔案夾/var/lib/mysql或者使用專用的導出工具。

備份資料庫：

通過mysqldump指令可以将指定的庫、表或全部的庫導出為SQL腳本，便于該指令在不同版本的MySQL伺服器上使用。

A執行導出操作：

格式1：導出指定庫中的部分表

Mysqldump [選項] 庫名 [表名1][表名2] . .>/備份路徑/備份檔案名

格式2：導出一個或多個完整的庫（包括其中所有的表）

Mysqldump [選項] --databases 庫名1[庫名2]… >/備份路徑/備份檔案名

格式3：備份mysql伺服器中的所有的庫

Mysqldump [選項] --all-databases >/備份路徑/備份檔案名

其中“ -u ”用來指定資料庫的使用者名；“ -p ”用來指定資料庫的密碼

Eg：将mysql庫中的user表中導出為/home/mysql.user.sql檔案，将整個mysql庫導出為/home/mysql.sql檔案，所有操作都以root使用者的身份驗證：

mysqldump -u root -p --set-gtid-purged=OFF mysql user > /home/mysql.user.sql

mysqldump -u root -p --set-gtid-purged=OFF --databases mysql > /home/mysql.sql

若要備份整個mysql伺服器的所有庫，應使用格式3，當導出的資料量較大時，可以添加‘--opt‘選項以優化執行速度。

Eg：建立備份檔案/home/all-databases.sql，其中包括mysql伺服器中的所有庫：

mysqldump -u root -p --set-gtid-purged=OFF --opt --all-databases > /home/all-databases.sql

B檢視備份檔案内容：

Eg：過濾出/home/all-databases.sql腳本中的資料庫操作語句：

grep -v "^--" /home/all-databases.sql |grep -v "^/" |grep -v "^$" |more

恢複資料庫：

格式： mysql [選項] [庫名] [表名] < /備份路徑/備份檔案名

當備份檔案中隻包含表的備份，而不包括建立庫的語句時，則執行導入操作時必須指定庫名，且目标庫必須存在。

Eg：從備份檔案中/home/mysql.user.sql中将表導入到mysql庫：

mysql -u root -p mysql < /home/mysql.user.sql

若備份檔案中已經包括完整的庫資訊，則執行導入操作時無需指定庫名。

Eg：從備份檔案/home/all-databases.sql恢複其中的所有庫：

mysql -u root -p < /home/all-databases.sql

日常維護MySQL常用sql

1.慢sql情況查詢：

可以使用以下三種方式查詢，第一種是了解MySQL程序大概情況；第二種是按照影響時間倒序的，可以查詢到目前最慢的一條sql；第三種是防止sql 的info消息過長而無法顯示完整。

了解MySQL程序大概情況

+----+------+--------------------+------+------------------+--------+-----------------------------------------------------------------------+------------------+

按照影響時間倒序的，可以查詢到目前最慢的一條sql

select * from information_schema.`PROCESSLIST` where info is not null order by time desc;

+----+------+-----------+------+---------+------+-----------+------------------------------------------------------------------------------------------+

1 row in set (0.01 sec)

防止sql 的info消息過長而無法顯示完整

show full processlist;

+----+------+--------------------+------+------------------+--------+-----------------------------------------------------------------------+-----------------------+

3 rows in set (0.01 sec)

補充一下，若出現大量慢sql，在不影響業務的前提下，可以将一些select先kill掉，然後來緩沖一下MySQL的性能問題

select concat('kill ',id,' ;') from information_schema.`PROCESSLIST` where info like 'select%' and time>3 order by time desc;

2.連接配接數的查詢：

可以使用以下sql查詢到目前執行個體下所有庫的連接配接數（由于該sql是根據同一個host來判斷的，是以可能存在一個ip，不同端口有多個連接配接）

select db,count(1) from information_schema.`PROCESSLIST` group by db order by 2 desc;

+------+----------+

| db | count(1) |

| NULL | 3 |

若想查詢到完整的host

select db,host from information_schema.`PROCESSLIST`

-> -- where db = dbname

-> ;

+------+--------------------+

| db | host |

| NULL | localhost |

| NULL | mysqlstandby:21541 |

| NULL | mysql3:5429 |

查詢有哪些host連接配接到目前執行個體，而不考慮host端口

select substring_index(host,':',1) ip,count(1) from information_schema.`PROCESSLIST` where db is null group by ip order by 2 desc;

+--------------+----------+

| ip | count(1) |

| localhost | 1 |

| mysql3 | 1 |

| mysqlstandby | 1 |

3.磁盤空間的預估：

主要是根據資料量和索引量來對一個執行個體進行預估磁盤容量，以下sql統計出來的結果都是以MB為機關

查某個資料庫的總容量（查資料庫mysql的總容量）

select round(sum(data_length+index_length)/1024/1024,2) as total_db_mb from information_schema.tables where table_schema like 'mysql%';

+-------------+

| total_db_mb |

| 0.88 |

查磁盤告警的rds的表容量

select table_schema,table_name,table_rows,round((data_length+index_length)/1024/1024,2) as total_mb from information_schema.tables

-> where -- table_schema in ('dbname') and

-> table_schema not in ('mysql','information_schema','performance_schema','sys_info')

-> -- and table_name in ('tbname1','tbname2')

-> order by total_mb desc;

+--------------+------------+------------+----------+

| sbtest | test | 0 | 0.05 |

| xiaoboluo | test | 4 | 0.02 |

| sbtest | test_log | 0 | 0.02 |

mysql日常維護

1.mysql權限管理

1.1 mysql賬戶權限資訊被存儲在mysql資料庫的user 、db、host、tables_priv、column_priv和procs_priv表中，mysql啟動時伺服器将這些資料庫表内容讀入記憶體。推薦grant和revoke語句

例如：

grant select,update on mysql.user to

建立成功後檢視mysql資料庫表的變化

select * from mysql.user where user='test' \G

Host: %

User: test

Password:

Select_priv: N

Insert_priv: N

Update_priv: N

Delete_priv: N

Create_priv: N

Drop_priv: N

Reload_priv: N

Shutdown_priv: N

Process_priv: N

File_priv: N

Grant_priv: N

References_priv: N

Index_priv: N

Alter_priv: N

Show_db_priv: N

Super_priv: N

Create_tmp_table_priv: N

Lock_tables_priv: N

Execute_priv: N

Repl_slave_priv: N

Repl_client_priv: N

Create_view_priv: N

Show_view_priv: N

Create_routine_priv: N

Alter_routine_priv: N

Create_user_priv: N

Event_priv: N

Trigger_priv: N

Create_tablespace_priv: N

ssl_type:

ssl_cipher:

x509_issuer:

x509_subject:

max_questions: 0

max_updates: 0

max_connections: 0

max_user_connections: 0

plugin: mysql_native_password

authentication_string:

password_expired: N

*************************** 2. row ***************************

Host: 192.168.1.10

Password: *2FF960917BC9231714223F746ECBC728FCC24E59

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

select * from mysql.db where user='test' \G

Empty set (0.01 sec)

select * from mysql.tables_priv where user='test' \G

Host: 192.168.1.10

Db: mysql

User: test

Table_name: user

Grantor:

root@localhost

Timestamp: 0000-00-00 00:00:00

Table_priv: Select,Update

Column_priv:

mysql權限按照user 、db、tables_priv、column_priv檢查順序，如果對應表權限為Y，則不會檢查後面的表

1.2.檢視或修改賬戶權限

show grants for

Grants for

[email protected]

: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON *.* TO

IDENTIFIED BY PASSWORD '*2FF960917BC9231714223F746ECBC728FCC24E59'

: GRANT SELECT, UPDATE ON `mysql`.`user` TO

1.3 回收賬戶權限

revoke usage on *.* from

2.mysql日志管理

mysql服務支援的日志有二進制日志、錯誤日志、通路日志和滿查詢日志。

2.1 二進制日志（binlog）

記錄所有DDL和DML操作，但不包括資料查詢語句，通過mysqlbinlog檢視例如：

mysqlbinlog mysql-bin.000001 |cat -n |more

2.1.1 删除binlog，用purge binary logs 例如：

删除指定序号之前的二進制日志

purge binary logs to 'mysql-bin.000005';

删除指定時間之前的二進制日志

purge binary logs before '2018-07-04 18:57:00';

2.1.2 指定參數設定二進制檔案保留天數“expire_logs_days=#”

自動清理14天之前的二進制日志檔案

set global expire_logs_days=14;

vi /usr/my.cnf

expire_logs_days=14

2.2 操作錯誤日志

通過設定“--log_error=[file-name]” 指定錯誤日志存放位置，如沒有設定，則錯誤日志預設位于mysql服務的datadir目錄下

log_error='/var/lib/mysql/mysql.err'

2.3 通路日志

記錄了所有關于用戶端發起的連結，查詢和更新語句，由于記錄了所有的操作，在相對繁忙的系統中建議關閉

在配置檔案“--log=[file-name]” 指定通路日志的位置，另外一種方法可以在登陸mysql執行個體後通過變量啟用此日志，如

show global variables like '%general_log%';

+------------------+--------------------------+

| Variable_name | Value |

| general_log | OFF |

| general_log_file | /var/lib/mysql/mysql.log |

set global general_log=on;

| general_log | ON |

2.4 慢查詢日志

記錄了時間超過參數long_query_time（機關是秒）所設定值的SQL語句日志，對于稽核和開發者發現性能問題及時優化有重要意義。

如需啟用該日志可以在配置檔案中設定 “slow_query_log" ，沒有指定檔案名，則預設hostname-slow.log作為檔案名，并存放在資料目錄中

show global variables like '%long_query_time%';

+-----------------+-----------+

| Variable_name | Value |

| long_query_time | 10.000000 |

show global variables like '%slow_query_log%';

+---------------------+-------------------------------+

| Variable_name | Value |

| slow_query_log | OFF |

| slow_query_log_file | /var/lib/mysql/mysql-slow.log |

開啟慢查詢日志功能

set global slow_query_log=on;

| slow_query_log | ON |

2 rows in set (0.01 sec)

slow_query_log=on

慢查詢日志分析工具 mysqldumpslow

-s 排序類型

怎樣排序這個輸出。排序類型的這個值應該從以下清單中選擇：

t, at: 按查詢時間來排序或者按平均查詢時間來排序

l, al: 按鎖定時間來排序或者按平均鎖定時間來排序

r, ar: 按傳回記錄數來排序或者按平均傳回記錄數來排序

c: Sort by count

預設情況下, mysqldumpslow按照平均查詢時間來排序(等于-s at)。

[-s] 排序類型，可選al:平均鎖定時間 ar:平均傳回記錄數 at：平均查詢時間；

[-t] 隻顯示指定的行數；

mysqldumpslow -s at /var/lib/mysql/mysql-slow.log

3. mysql備份與恢複

3.1 備份方式可以通過直接備份資料檔案或使用mysqldump指令将資料庫資料導出到文本檔案，直接備份資料庫檔案适用于MyISAM和InnoDB存儲引擎，由于備份時資料庫表正在讀寫，備份出的檔案可能損壞無法使用，不推薦直接使用此方法。

導出資料庫sbtest

mysqldump -u root -p --set-gtid-purged=OFF sbtest > /home/sbtest.sql

導出一個表mysql.user

隻導出資料庫表結構 -d沒有資料 --add-drop-table 在每個create語句前增加一個drop table

mysqldump -u root -p --set-gtid-purged=OFF -d --add-drop-table sbtest > /home/sbtest.sql

恢複資料方法1

mysql -u root -p sbtest < /home/sbtest.sql

恢複資料方法2

source /home/sbtest.sql

3.2 另外一種可以實時備份的開源工具為xtrabackup

http://www.percona.com/downloads/

提供下載下傳

Xtrabackup是由 Percona 開發的一個開源軟體，可實作對 InnoDB 的資料備份，支援線上熱備份（備份時不影響資料讀寫）。備份時，Xtrabackup 會将 Master 的 binlog 資訊記錄在 xtrabackup_slave_info 檔案中，通過此資訊可以友善的搭建主從複制。

XtraBackup 有兩個工具：xtrabackup 和 innobackupex：

xtrabackup 本身隻能備份 InnoDB 和 XtraDB ，不能備份 MyISAM；

innobackupex 本身是 Hot Backup 腳本修改而來，同時可以備份 MyISAM 和 InnoDB，但是備份 MyISAM 需要加讀鎖。

官網：

http://www.percona.com/software/percona-xtrabackup

文檔：

http://www.percona.com/doc/percona-xtrabackup/2.2/index.html

安裝 XtraBackup（主從庫都要操作）

vi /etc/resolv.conf

nameserver 192.168.1.1

nameserver 8.8.8.8

1、添加源

yum install

https://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm

2、安裝 xtrabackup

從這個位置E:\zachary\mysql\上傳以下兩個軟體到伺服器的/software目錄

libev-4.15-3.el7.x86_64.rpm

Percona-XtraBackup-2.4.11-rb4e0db5-el7-x86_64-bundle.tar

cd /software

rpm -ivh libev-4.15-3.el7.x86_64.rpm

tar -xvf Percona-XtraBackup-2.4.11-rb4e0db5-el7-x86_64-bundle.tar

yum install rsync perl-DBD-MySQL perl-Digest-MD5

rpm -ivh percona-xtrabackup-24-2.4.11-1.el7.x86_64.rpm

建立備份

innobackupex --user=root --password=jge64NSU /home

如果執行正确，其輸出資訊通常類似：

180602 07:05:25 Executing UNLOCK TABLES

180602 07:05:25 All tables unlocked

180602 07:05:25 Backup created in directory '/home/2018-06-02_07-05-20/'

MySQL binlog position: filename 'master-bin.000009', position '120'

180602 07:05:25 [00] Writing /home/2018-06-02_07-05-20/backup-my.cnf

180602 07:05:25 [00] ...done

180602 07:05:25 [00] Writing /home/2018-06-02_07-05-20/xtrabackup_info

xtrabackup: Transaction log of lsn (1684441) to (1684441) was copied.

180602 07:05:25 completed OK!

備份時，innobackupex 會調用 xtrabackup 備份 InnoDB 表的資料，并且會複制 MyISAM, MERGE，CSV 和 ARCHIVE 表的表定義檔案（.frm 檔案）、資料檔案。同時還會備份觸發器和資料庫配置資訊相關的檔案。這些檔案将會儲存在指定備份目錄中一個以時間戳命名的目錄下。

準備備份

一般情況下，在備份完成後，資料尚且不能用于恢複操作，因為備份的資料中可能會包含尚未送出的事務或已經送出但尚未同步至資料檔案中的事務。是以，此時資料檔案仍處理不一緻狀态。“準備”的主要作用正是通過復原未送出的事務及同步已經送出的事務至資料檔案也使得資料檔案處于一緻性狀态。

innobackupex --apply-log /home/2018-06-02_07-05-20

如果執行正确，其最後輸出的幾行資訊通常如下：

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: page_cleaner: 1000ms intended loop took 8226ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 1685544

180602 07:15:20 completed OK!

在實作“準備”的過程中，innobackupex 通常還可以使用 --use-memory 選項來指定其可以使用的記憶體的大小，預設通常為 100M。如果有足夠的記憶體可用，可以多劃分一些記憶體給 prepare 的過程，以提高其完成速度。

恢複備份

将資料複制到從伺服器上：

scp -r /home/2018-06-02_07-05-20

[email protected]:/home

在從伺服器中恢複備份資料：（從庫上操作）

mv -f /var/lib/mysql /var/lib/`date +%Y_%m%d`_mysql_BACKUP

mkdir /var/lib/mysql

chown mysql:mysql /var/lib/mysql

innobackupex --datadir=/var/lib/mysql --copy-back /home/2018-06-02_07-05-20

如果伺服器剩餘空間不足，你可以使用 --move-back 替換掉 --copy-back。

如果執行正确，其輸出資訊的最後幾行通常如下：

180602 08:22:12 [01] Copying ./performance_schema/session_account_connect_attrs.frm to /var/lib/mysql/performance_schema/session_account_connect_attrs.frm

180602 08:22:12 [01] ...done

180602 08:22:12 [01] Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info

180602 08:22:12 [01] Copying ./xtrabackup_binlog_pos_innodb to /var/lib/mysql/xtrabackup_binlog_pos_innodb

180602 08:22:12 [01] Copying ./xtrabackup_master_key_id to /var/lib/mysql/xtrabackup_master_key_id

180602 08:22:12 [01] Copying ./ibtmp1 to /var/lib/mysql/ibtmp1

180602 08:22:12 completed OK!

chown -R mysql:mysql /var/lib/mysql

chmod -R g+w /var/lib/mysql

chmod -R o+x /var/lib/mysql/mysql

chcon -R -t mysqld_db_t /var/lib/mysql

service mysql start

4.mysql複制

複制功能可以經濟高效地提高應用程式的性能、擴充力和高可用性。既支援簡單的主從拓撲，也可以實作複雜、極具可伸縮性的鍊式叢集。

使用mysql複制時，所有對複制表的更新必須在主伺服器上進行，否則可能引起主伺服器上的表進行的更新與對從伺服器上的表所進行的更新産生沖突。

好處：（1）主從資料同步，主伺服器故障時，從伺服器可作為主伺服器接管服務。

（2）負載均衡。實作讀寫分離，主伺服器記錄更新、删除、插入等操作；從伺服器隻查詢請求；

（3）資料備份。從伺服器可設定在異地，增加容災的健壯性，為避免傳輸過慢，可設定參數slave_compressed_protocol 啟用binlog壓縮傳輸

show global variables like '%slave_compressed_protocol%';

+---------------------------+-------+

| Variable_name | Value |

| slave_compressed_protocol | OFF |

set global slave_compressed_protocol=on;

| slave_compressed_protocol | ON |

mysql使用3個線程來執行複制功能，1個在主伺服器上，2個在從伺服器上。當執行START SLAVE時，主伺服器建立一線程負責發送二進制日志。從伺服器建立一個I/O線程，負責讀取主伺服器上的二進制日志，然後将該資料儲存到從伺服器資料目錄中的中繼日志檔案中。從伺服器的SQL線程負責讀取中繼日志并重做日志中包含的更新，進而達到主從資料庫資料的一緻性。#在主從伺服器上輸入指令查詢狀态：mysql>show processlist \G 其中time列的值可以顯示從伺服器比主伺服器滞後多長時間。

master操作：

slave操作：

+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+------------------+

啟動：

nohup mysqld_safe --defaults-file=/usr/my.cnf 2>&1 > /dev/null &

關閉：

mysqladmin -u root -p -S /var/lib/mysql/mysql.sock shutdown

檢視mysql端口：

netstat -tulpn |grep -i mysql

tcp6 0 0 :::3306 :::* LISTEN 8681/mysqld

ss -tulpn |grep -i mysql

tcp LISTEN 0 80 :::3306 :::* users:(("mysqld",pid=8681,fd=15))

登入mysql：

mysql -u root -p -S /var/lib/mysql/mysql.sock

遠端登入，指定IP、端口

mysql -u root -p -h 192.168.1.14 -P 3306

清屏：

mysql> system clear;

臨時修改mysql提示符：

prompt \u@\h \d \r:\m:\s>

PROMPT set to '\u@\h \d \r:\m:\s>'

(none) 04:39:01>

永久修改mysql提示符：（無需重新開機）

參考：

http://www.2cto.com/database/201304/201745.html

或在shell中添加環境變量

vi /etc/profile

MYSQL_PS1="\u@\h \d \r:\m:\s>";export MYSQL_PS1

su -

重新整理權限

删除使用者

建立使用者并授權

grant all privileges on sbtest.* to

'test3'@'%'

identified by 'test3';

檢視權限

+--------------------------------------------------------------------------------------------------------------+

| Grants for

test2@localhost

| GRANT USAGE ON *.* TO

IDENTIFIED BY PASSWORD '*7CEB3FDE5F7A9C4CE5FBE610D7D8EDA62EBE5F4E' |

| GRANT ALL PRIVILEGES ON `sbtest`.* TO

+------------------------------------------------------------------------------------------------------+

test3@%

IDENTIFIED BY PASSWORD '*F357E78CABAD76FD3F1018EF85D78499B6ACC431' |

MySQL重要目錄

資料庫目錄　/var/lib/mysql

配置檔案　 /usr/share/mysql

指令目錄　/usr/bin(mysqladmin mysqldump等指令)

啟動腳本　 /etc/rc.d/init.d/mysql

重新啟動mysql服務

service mysql restart

Shutting down MySQL.... SUCCESS!

Starting MySQL. SUCCESS!

檢查mysql3306狀态

netstat -nat

Active Internet connections (servers and established)

Proto Recv-Q Send-Q Local Address Foreign Address State

tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN

tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN

tcp 0 52 192.168.1.11:22 192.168.1.10:49576 ESTABLISHED

tcp6 0 0 :::3306 :::* LISTEN

tcp6 0 0 :::22 :::* LISTEN

tcp6 0 0 ::1:25 :::* LISTEN

tcp6 0 0 192.168.1.11:3306 192.168.1.14:5464 ESTABLISHED

tcp6 0 0 192.168.1.11:3306 192.168.1.12:21576 ESTABLISHED

檢查mysql活動狀态

mysqladmin -u root -p ping

Enter password:

mysqld is alive

傳回伺服器狀态

mysqladmin -u root -p status

Uptime: 632 Threads: 3 Questions: 30 Slow queries: 0 Opens: 70 Flush tables: 1 Open tables: 63 Queries per second avg: 0.047

關閉MySQL服務

mysqladmin -u root -p shutdown

顯示伺服器版本

mysqladmin -u root -p version

mysqladmin Ver 8.42 Distrib 5.6.40, for Linux on x86_64

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Server version 5.6.40-log

Protocol version 10

Connection Localhost via UNIX socket

UNIX socket /var/lib/mysql/mysql.sock

Uptime: 4 min 53 sec

Threads: 3 Questions: 30 Slow queries: 0 Opens: 70 Flush tables: 1 Open tables: 63 Queries per second avg: 0.102

修改mysql管理者密碼

mysqladmin -u root -p password 'jge64NSU' ###單引号裡面的為新密碼

Enter password: 輸入舊密碼

Warning: Using a password on the command line interface can be insecure.

mysql添加新使用者

格式：grant 權限 on 資料庫.* to 使用者名@登入主機 identified by "密碼"

grant select,insert,update,delete on *.* to

grant all on sbtest.* to

'test3'@'localhost'

=所有網絡

=隻能本地伺服器登入

'test3'@'192.168.0.10'

=指定IP登入

'test3'@'192.168.0.%'=192.168.0/254

網絡登入

mysql撤銷使用者權限

格式： revoke 權限 on 資料庫.表 from 使用者名@登入用戶端;

revoke insert,delete on *.* from

revoke all on sbtest.* from

mysql删除使用者

檢視mysql使用者

select host,user,password from mysql.user;

+--------------+------+-------------------------------------------+

| host | user | password |

| localhost | root | *919C4FEEA34F61D80C2784EA3B9E5AEA260BA29D |

| 127.0.0.1 | root | |

| ::1 | root | |

| 192.168.1.12 | repl | *A65B51DC9961BDCD4A93BE9063689226163D7343 |

| % | repl | |

| % | test | |

| 192.168.1.10 | test | *2FF960917BC9231714223F746ECBC728FCC24E59 |

| 192.168.1.13 | test | *2FF960917BC9231714223F746ECBC728FCC24E59 |

| 192.168.1.11 | test | *2FF960917BC9231714223F746ECBC728FCC24E59 |

| 192.168.1.12 | test | *2FF960917BC9231714223F746ECBC728FCC24E59 |

| 192.168.1.11 | repl | *A65B51DC9961BDCD4A93BE9063689226163D7343 |

| 192.168.1.14 | repl | *A65B51DC9961BDCD4A93BE9063689226163D7343 |

| 192.168.1.11 | root | *919C4FEEA34F61D80C2784EA3B9E5AEA260BA29D |

| 192.168.1.12 | root | *919C4FEEA34F61D80C2784EA3B9E5AEA260BA29D |

| 192.168.1.14 | root | *919C4FEEA34F61D80C2784EA3B9E5AEA260BA29D |

| 192.168.1.15 | root | *919C4FEEA34F61D80C2784EA3B9E5AEA260BA29D |

| 192.168.1.26 | root | *919C4FEEA34F61D80C2784EA3B9E5AEA260BA29D |

| 192.168.1.% | test | *2FF960917BC9231714223F746ECBC728FCC24E59 |

18 rows in set (0.00 sec)

檢視連接配接伺服器使用者線程

殺掉使用者線程

mysql> kill 11;

ERROR 1317 (70100): Query execution was interrupted

mysql資料庫備份恢複

顯示資料庫sbtest内容

mysqldump -u root -p --set-gtid-purged=OFF sbtest |more

備份多個表，備份資料庫sbtest的表test和表test_log

mysqldump -u root -p --set-gtid-purged=OFF sbtest test test_log > /home/sbtest.test_test_log.sql

備份單一資料庫sbtest

備份多個資料庫，備份資料庫sbtest和資料庫mysql

mysqldump -u root -p --set-gtid-purged=OFF -B sbtest mysql > /home/sbtest_mysql.sql

備份所有資料庫

mysqldump -u root -p --set-gtid-purged=OFF --all-databases > /home/all-databases.sql

恢複資料庫

source /home/sbtest.sql;

原文位址https://blog.csdn.net/zacharyzhong/article/details/81132110

MySQL日常維護（超級大雜燴）MySQL日常維護（超級大雜燴）

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++驅動概述