MySQL 細緻總結之基礎篇

1、使用幫助資訊

mysql> help create;
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
   CREATE DATABASE   #最簡單的建立命令
   CREATE EVENT
   CREATE FUNCTION
   CREATE FUNCTION UDF
   CREATE INDEX
   CREATE LOGFILE GROUP
   CREATE PROCEDURE
   CREATE SERVER
   CREATE TABLE
   CREATE TABLESPACE
   CREATE TRIGGER
   CREATE USER
   CREATE VIEW
   SHOW
   SHOW CREATE DATABASE
   SHOW CREATE EVENT
   SHOW CREATE FUNCTION
   SHOW CREATE PROCEDURE
   SHOW CREATE TABLE
   SPATIAL

2、建立、刪除、檢視資料庫

mysql> create database test_data;
   #建立預設字符集的資料庫（預設是拉丁字符集）
Query OK, 1 row affected (0.02 sec)
mysql> show databases like "test%";
+------------------+
| Database (test%) |
+------------------+
| test_data        |
+------------------+
1 rows in set (0.00 sec)

建立gbk字符集的資料庫

mysql> create database test_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
Query OK, 1 row affected (0.04 sec)
mysql> show create database test_gbk; 

檢視建立資料庫的語句

+----------+------------------------------------------------------------------+
| Database | Create Database                                                    |
+----------+------------------------------------------------------------------+
| test_gbk | CREATE DATABASE `test_gbk` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

刪除資料庫

mysql> drop database test_data;
Query OK, 0 rows affected (0.07 sec)
mysql> show databases;
+----------------------------+
| Database                   |
+----------------------------+
| information_schema |
| test_gbk                    |
+----------------------------+

3、連線資料庫

mysql> use test_gbk;

相當於cd命令，切換到資料庫進行操作

Database changed
mysql> select database();

檢視當前連線的資料庫，相當於pwd

+------------+
| database() |
+------------+
| test_gbk   |
+------------+
1 row in set (0.00 sec)
mysql> select user();

檢視當前連線資料庫的使用者,相當於whoami

+--------------------+
| user()               |
+-------------------+
| root@localhost |
+--------------------+
1 row in set (0.00 sec)

4、建立使用者、授權、收回許可權

當資料庫建立完成後，就需要建立使用者，以供需要連線資料庫的人員使用與運算元據庫，不可能人人使用root登陸，所以許可權設定也是很重要的

mysql> grant all on test_gbk.* to 'testuser'@'localhost' identified by '123456';

建立使用者並all許可權給在test_gbk庫所有表，密碼‘123456’

Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;

重新整理許可權，使許可權生效

Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'testuser'@'localhost';

檢視使用者有哪些許可權

+-----------------------------------------------------------------------------------------------------------------+
| Grants for testuser@localhost                                                                                                  |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT ALL PRIVILEGES ON `test_gbk`.* TO 'testuser'@'localhost'                                  |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

收回許可權

mysql> revoke insert,update,select,delete on test_gbk.* from 'testuser'@'localhost';

將以上許可權收回

Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'testuser'@'localhost';                                       
+----------------------------------------------------------------------------------------------------+
| Grants for testuser@localhost                                                                          |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'           |
| GRANT CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test_gbk`.* TO 'testuser'@'localhost' |
+------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

注：原來不知道all許可權到底是哪些許可權，採用這種方法之後，應該就清楚了

SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER

5、建立、刪除表

mysql> create table test(id int(4)not null,name char(20)not null);

建表，並且建立兩個欄位

Query OK, 0 rows affected (0.06 sec)
mysql> show tables;#檢視錶
+--------------------+
| Tables_in_test_gbk |
+--------------------+
| test                     |
+--------------------+
1 row in set (0.00 sec)
mysql> desc test; #檢視錶結構
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(4)   | NO   |     | NULL    |       |
| name  | char(20) | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql>create table test1(id int(4)not null,name char(20)not null);
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+------------------------+
| Tables_in_test_gbk |
+------------------------+
| test                        |
| test1                     |
+--------------------+
2 rows in set (0.00 sec)
刪除表
mysql> drop tables test;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+--------------------+
| Tables_in_test_gbk |
+--------------------+
| test1                 |
+--------------------+
1 row in set (0.00 sec)

檢視建表

mysql>show create table test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `id` int(4) NOT NULL,
  `name` char(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

1、什麼是多例項

•  多例項就是一臺伺服器上開啟多個不同的服務埠（預設3306），執行多個mysql的服務程式，這此服務程式通過不同的socket監聽不同的服務埠來提供各在的服務，所有例項之間共同使用一套MYSQL的安裝程式，但各自使用不同的配置檔案、啟動程式、資料檔案，在邏輯上是相對獨立的。
• 多例項主要作用是：充分利用現有的伺服器硬體資源，為不同的服務提供資料服務，但是如果某個例項併發比較高的，同樣是會影響到其它例項的效能

2、安裝多例項環境準備

安裝前需要先安裝mysql，但是隻需將安裝過程進行到make install即可（編譯安裝），如果使用免安裝程式，只需解壓軟體包即可，今天的環境是通過免安裝包來安裝mysql主程式（其它的安裝可以參考前面的安裝過程自行測試）

系統環境

[root@centos6 ~]# cat /etc/redhat-release 
CentOS release 6.5 (Final)
[root@centos6 ~]# uname -r
2.6.32-431.el6.x86_64

安裝程式

mysql-5.5.52-linux2.6-x86_64.tar.gz
#首先將軟體下載到本地
wget http://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.52-linux2.6-x86_64.tar.gz

建立安裝使用者

[root@centos6 ~]#groupadd mysql
[root@centos6 ~]#useradd mysql -s /sbin/nologin -g mysql -M
[root@centos6 ~]#tail -1 /etc/passwd
mysql:x:500:500::/home/mysql:/sbin/nologin

建立多例項的資料目錄

[root@centos6 tools]# mkdir -p /data/{3306,3307}
[root@centos6 tools]# tree /data/
/data/
+-- 3306
+-- 3307
2 directories, 0 files

3、安裝MYSQL多例項

接下來進行安裝mysql的多例項操作

解壓軟體

[root@centos6 tools]# ll mysql-5.5.52-linux2.6-x86_64.tar.gz 
-rw-r--r--. 1 root root 185855000 Aug 26 21:38 mysql-5.5.52-linux2.6-x86_64.tar.gz
[root@centos6 tools]# tar zxf mysql-5.5.52-linux2.6-x86_64.tar.gz

拷貝配置檔案

[root@centos6 mysql-5.5.52-linux2.6-x86_64]# cp support-files/my-small.cnf /data/3306/my.cnf
[root@centos6 mysql-5.5.52-linux2.6-x86_64]# cp support-files/mysql.server /data/3306/mysql
[root@centos6 mysql-5.5.52-linux2.6-x86_64]# cp support-files/my-small.cnf /data/3307/my.cnf
[root@centos6 mysql-5.5.52-linux2.6-x86_64]# cp support-files/mysql.server /data/3307/mysql

為一規範安裝路徑，將免安裝包拷貝到應用程式目錄下

[root@centos6 tools]# mv mysql-5.5.52-linux2.6-x86_64 /application/mysql
[root@centos6 tools]# ll /application/mysql
total 72
drwxr-xr-x.  2 root root   4096 Dec  9 17:15 bin
-rw-r--r--.  1 7161 31415 17987 Aug 26 19:24 COPYING
drwxr-xr-x.  3 root root   4096 Dec  9 17:15 data
drwxr-xr-x.  2 root root   4096 Dec  9 17:15 docs
drwxr-xr-x.  3 root root   4096 Dec  9 17:15 include
-rw-r--r--.  1 7161 31415   301 Aug 26 19:24 INSTALL-BINARY
drwxr-xr-x.  3 root root   4096 Dec  9 17:15 lib
drwxr-xr-x.  4 root root   4096 Dec  9 17:15 man
drwxr-xr-x. 10 root root   4096 Dec  9 17:15 mysql-test
-rw-r--r--.  1 7161 31415  2496 Aug 26 19:24 README
drwxr-xr-x.  2 root root   4096 Dec  9 17:15 scripts
drwxr-xr-x. 27 root root   4096 Dec  9 17:15 share
drwxr-xr-x.  4 root root   4096 Dec  9 17:15 sql-bench
drwxr-xr-x.  2 root root   4096 Dec  9 17:15 support-files

修改配置檔案與啟動檔案

配置檔案my.cnf

[client]
port = 3307
socket = /data/3307/mysql.sock
[mysql]
no-auto-rehash
[mysqld] user = mysql
port = 3307
socket = /data/3307/mysql.sock
basedir = /application/mysql
datadir = /data/3307/data
#log_long_format
#log-error = /data/3307/error.log
#log-slow-queries = /data/3307/slow.log
pid-file = /data/3307/mysql.pid
server-id = 3    
[mysqld_safe]
log-error=/data/3307/mysql3307.err
pid-file=/data/3307/mysqld.pid

啟動程式檔案mysql

[root@backup 3307]# cat mysql
#!/bin/sh
init port=3307
mysql_user="root"
mysql_pwd="migongge"
CmdPath="/application/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
#startup
function_start_mysql() {
if [ ! -e "$mysql_sock" ];then
 printf "Starting MySQL...\n"
/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &
else
  printf "MySQL is running...\n"
exit
fi
}
#stop function
function_stop_mysql() {
if [ ! -e "$mysql_sock" ];then
printf "MySQL is stopped...\n"
exit
else
printf "Stoping MySQL...\n"
${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown
fi
}
#restart function
function_restart_mysql() {
   printf "Restarting MySQL...\n"
   function_stop_mysql
   sleep 2
   function_start_mysql
}
case $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
esac

其它的配置可參考配置檔案進行修改即可

多例項初始化操作

[root@centos6 3306]#** /application/mysql/scripts/mysql_install_db --basedir=/application/mysql --datadir=/data/3306/data --user=mysql**
Installing MySQL system tables...
161209 18:02:17 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
161209 18:02:17 [Note] /application/mysql/bin/mysqld (mysqld 5.5.52-log) starting as process 3336 ...
OK
Filling help tables...
161209 18:02:17 [Warning] 'THREAD_CONCURRENCY' is deprecated and will be removed in a future release.
161209 18:02:17 [Note] /application/mysql/bin/mysqld (mysqld 5.5.52-log) starting as process 3343 ...
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/application/mysql/bin/mysqladmin -u root password 'new-password'
/application/mysql/bin/mysqladmin -u root -h centos6 password 'new-password'
Alternatively you can run:
/application/mysql/bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd /application/mysql ; /application/mysql/bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd /application/mysql/mysql-test ; perl mysql-test-run.pl
Please report any problems at http://bugs.mysql.com/

初始化成功後，會在資料目錄下產生一個資料目錄data和一些檔案

[root@centos6 3306]# ll /data/3306/data/
total 1136
drwx------. 2 mysql root     4096 Dec  9 18:02 mysql
-rw-rw----. 1 mysql mysql   27693 Dec  9 18:02 mysql-bin.000001
-rw-rw----. 1 mysql mysql 1114546 Dec  9 18:02 mysql-bin.000002
-rw-rw----. 1 mysql mysql      38 Dec  9 18:02 mysql-bin.index
drwx------. 2 mysql mysql    4096 Dec  9 18:02 performance_schema
drwx------. 2 mysql root     4096 Dec  9 18:02 test

另一個例項的初始化請參考上述操作進行

[root@centos6 3307]# ll /data/3307/data/
total 1136
drwx------. 2 mysql root     4096 Dec  9 18:40 mysql
-rw-rw----. 1 mysql mysql   27693 Dec  9 18:40 mysql-bin.000001
-rw-rw----. 1 mysql mysql 1114546 Dec  9 18:40 mysql-bin.000002
-rw-rw----. 1 mysql mysql      38 Dec  9 18:40 mysql-bin.index
drwx------. 2 mysql mysql    4096 Dec  9 18:40 performance_schema
drwx------. 2 mysql root     4096 Dec  9 18:40 test

4 、啟動多例項並登入

啟動服務

[root@backup 3307]# /data/3306/mysql start
Starting MySQL...
[root@backup 3307]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 19986 mysql 10u IPv4 90967 0t0 TCP *:mysql (LISTEN)
[root@backup 3307]# /data/3307/mysql
start Starting MySQL...
[root@backup 3307]# lsof -i :3307
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 21648 mysql 11u IPv4 92899 0t0 TCP *:opsession-prxy (LISTEN)

檢查埠

[root@backup 3307]# netstat -lntup|grep mysql
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 21648/mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 19986/mysqld

登陸多例項資料庫

[root@backup ~]# mysql -S /data/3306/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.51-log Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database data3306;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| data3306 |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> quit
Bye
[root@backup ~]# mysql -S /data/3307/mysql.sock
Welcome to the MySQL monitor.
Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.51 Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.05 sec)

成功登陸，並在3306例項中建立資料庫，但是3307例項上檢視並沒有建立過的資料，說明兩個例項是獨立的

注：如果再需要新增一個例項，基本的配置步驟同上述一樣，只需要相應修改配置檔案與啟動程式檔案中的埠號與資料目錄的路徑即可，最後可以將多例項資料庫啟動命令加入開機自啟動

首先來看看資料庫中的資料

mysql> select * from test;
+-----+------+
| id  | name |
+-----+------+
|   1 | 1       |
|  11 | text  |
|  21 | abc  |
|   9 | bcd   |
| 111 | 1     |
| 441 | text |
|  41 | abc  |
| 999 | bcd  |
+-----+------+
8 rows in set (0.00 sec)

1、單庫備份

[root@centos6 ~]# mysqldump -uroot -p test >/download/testbak_$(date +%F).sql
Enter password: 
[root@centos6 ~]# ll /download/
total 2
-rw-r--r--.  1 root root 1888 Dec 12 20:34 testbak_2016-12-12.sql

下面我們看看這個備份檔案到底是什麼內容

[root@centos6 ~]# egrep -v "^--|\*|^$" /download/testbak_2016-12-12.sql
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(4) NOT NULL,
  `name` char(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES (1,'1'),(11,'text'),(21,'abc'),(9,'bcd'),(111,'1'),(441,'text'),(41,'abc'),(999,'bcd');
UNLOCK TABLES;

由上的檔案內容，可以看出，這個備份實際的過程就是將建立資料庫、建表、插入資料的sql語句備份出來，也可以說是將sql語句匯出

-B引數

[root@centos6 ~]# mysqldump -uroot -p -B test >/download/testbak_$(date +%F)_b.sql
Enter password: 
[root@centos6 ~]# egrep -v "^--|^$" /download/testbak_2016-12-12_b.sql 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `test`;
DROP TABLE IF EXISTS `test`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
  `id` int(4) NOT NULL,
  `name` char(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
LOCK TABLES `test` WRITE;
/*!40000 ALTER TABLE `test` DISABLE KEYS */;
INSERT INTO `test` VALUES (1,'1'),(11,'text'),(21,'abc'),(9,'bcd'),(111,'1'),(441,'text'),(41,'abc'),(999,'bcd');
/*!40000 ALTER TABLE `test` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-B引數的作用一目瞭然，就是當我們的資料庫丟失時，可以直接用此備份檔案進行恢復，無需再重新建庫、建表，然後再進行資料恢復的操作

2、壓縮備份

有時候，資料庫的資料比較大，可能會用到壓縮後進行備份，節省備份時間與磁碟空間的使用

[root@centos6 ~]# mysqldump -uroot -p -B test|gzip >/download/testbak_$(date +%F).sql.gz
Enter password: 
[root@centos6 ~]# ll /download/testbak_2016-12-12.sql.gz
-rw-r--r--. 1 root root 753 Dec 12 20:49 /download/testbak_2016-12-12.sql.gz
[root@centos6 ~]# ll /download/
total 14
-rw-r--r--.  1 root root **2027** Dec 12 20:41 testbak_2016-12-12_b.sql
-rw-r--r--.  1 root root **1888 **Dec 12 20:34 testbak_2016-12-12.sql
-rw-r--r--.  1 root root  753 Dec 12 20:49 testbak_2016-12-12.sql.gz

同時也可以看的壓縮後的效果

3、多庫備份

[root@centos6 ~]# mysqldump -uroot -p -B test mysql|gzip >/download/testbak_$(date +%F).sql01.gz
Enter password: 
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
[root@centos6 ~]# ll /download/testbak_2016-12-12.sql01.gz 
-rw-r--r--. 1 root root 152696 Dec 12 20:52 /download/testbak_2016-12-12.sql01.gz

此處有個警告資訊，可以忽略也可以備份時加上引數，備份語句如下

[root@centos6 ~]# ****mysqldump -uroot -p -B --events test mysql|gzip >/download/testbak_$(date +%F).sql02.gz
Enter password: 
[root@centos6 ~]# ll /download/testbak_2016-12-12.sql02.gz                                  
-rw-r--r--. 1 root root 152749 Dec 12 20:54 /download/testbak_2016-12-12.sql02.gz
這樣就不會有這為警告資訊了

但是這種多庫一起備份，就會產生一個問題，如果只是其中一個資料庫有問題了，就不好進行單庫恢復了，故此備份方法不常用，也不符合實際需求，因此多庫備份時就需要進行多次單庫備份的操作

[root@centos6 ~]# mysqldump -uroot -p -B test|gzip >/download/testbackup_$(date +%F).sql.gz                 
Enter password: 
[oot@centos6 ~]# mysqldump -uroot -p -B --events mysql|gzip >/download/mysqlbak_$(date +%F).sql.gz        
Enter password: 
[root@centos6 ~]# ll /download/
total 80
-rw-r--r--.  1 root root 152608 Dec 12 20:58 mysqlbak_2016-12-12.sql.gz
-rw-r--r--.  1 root root    754 Dec 12 20:58 testbackup_2016-12-12.sql.gz
-rw-r--r--.  1 root root   2027 Dec 12 20:41 testbak_2016-12-12_b.sql
-rw-r--r--.  1 root root   1888 Dec 12 20:34 testbak_2016-12-12.sql
-rw-r--r--.  1 root root 152696 Dec 12 20:52 testbak_2016-12-12.sql01.gz
-rw-r--r--.  1 root root 152749 Dec 12 20:54 testbak_2016-12-12.sql02.gz
-rw-r--r--.  1 root root    753 Dec 12 20:49 testbak_2016-12-12.sql.gz

4、單表備份

分庫備份是為了恢復資料庫時方便操作，但是同樣面臨問題，如果是某個庫中的某一個表有損壞，但又不有全庫進行恢復，所以實際生產中常用的是分庫、分表進行備份，這樣資料也備份了，恢復時也好操作

[root@centos6 ~]# mysqldump -uroot -p -B test test >/download/test_testbak_$(date +%F).sql      
Enter password: 
[root@centos6 ~]# egrep -v "#|^$|\*" /download/test_testbak_2016-12-12.sql
-- MySQL dump 10.13  Distrib 5.5.52, for linux2.6 (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version       5.5.53-log
--
-- Current Database: `test`
--
USE `test`;
--
-- Table structure for table `test`
--
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(4) NOT NULL,
  `name` char(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `test`
--
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES (1,'1'),(11,'text'),(21,'abc'),(9,'bcd'),(111,'1'),(441,'text'),(41,'abc'),(999,'bcd');
UNLOCK TABLES;
--
-- Current Database: `test`
--
USE `test`;
--
-- Table structure for table `test`
--
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(4) NOT NULL,
  `name` char(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `test`
--
LOCK TABLES `test` WRITE
INSERT INTO `test` VALUES (1,'1'),(11,'text'),(21,'abc'),(9,'bcd'),(111,'1'),(441,'text'),(41,'abc'),(999,'bcd');
UNLOCK TABLES;
-- Dump completed on 2016-12-12 21:13:16

因此分表備份同分庫備份一樣，只需要進行多次單表備份的操作，但是有的小夥伴肯定會提出問題了，如果一個庫裡幾千張表，幾萬張表，這種備份要備到猴年馬月吧？？？？，資料量比較大的備份可以使用專業的備份工具，資料量不大或者表不是很多的情況，可以將備份操作寫成指令碼 納入定時任務，定時執行，只需要檢查備份是否成功即可

實際生產環境中一個簡單的備份指令碼，僅供參考

[root@centos6 scripts]# vi bak.sh 
#!/bin/sh
##########################################
#this scripts create by root of mingongge
#create at 2016-11-11
#######################################
ip=`grep 'IPADDR' /etc/ysconfig/network-scripts/ifcfg-eth0|awk -F "=" '{print $2}'`
 #定義伺服器IP變數
BAKDIR=/backup  
  #定義備份路徑
[ ! -d $BAKDIR/${ip} ] && mkdir -p $BAKDIR/${ip}
 #判斷如果不存在這個路徑就建立一個，為了伺服器多的時候方便看
DB_PWD="mingongge"
DB_USER="root"
MYSQL="/application/mysql/bin/mysql"
MYSQL_DUMP="/application/mysql/bin/mysqldump"
DATA=`date +%F`
####bak data of test's databses####
DB_NAME=`$MYSQL -u$DB_USER -p$DB_PWD -e "show databases;"|sed '1,5d'`
  #定義資料庫變數
for name in $DB_NAME
#for迴圈語句取庫名
do
  $MYSQL_DUMP -u$DB_USER -p$DB_PWD -B ${name} |gzip >$BAKDIR/${ip}/${name}_$DATA.sql.gz  
 #全庫備份
  [ ! -d $BAKDIR/${ip}/${name} ] && mkdir -p  $BAKDIR/${ip}/${name}
#判斷這個路徑，為了區別哪個庫的備份檔案
  for tablename in `$MYSQL -u$DB_USER -p$DB_PWD -e "show tables from ${name};"|sed '1d'`
#for迴圈語句取表名
  do
   $MYSQL_DUMP -u$DB_USER -p$DB_PWD ${name} ${tablename} |gzip >$BAKDIR/${ip}/${name}/${tablename}_$DATA.sql.gz
#分表備份
  done
done

執行的結果如下

[root@ranzhioa ~]# tree /backup/
/backup/
10.1xx.1xx.1xx   #伺服器IP
  xxxxxxx           #其實是庫名
      cash_balance_2016-12-15.sql.gz
      cash_depositor_2016-12-15.sql.gz
      cash_trade_2016-12-15.sql.gz
        crm_customer_2016-12-15.sql.gz
         crm_delivery_2016-12-15.sql.gz
        crm_order_2016-12-15.sql.gz
        crm_orderAction_2016-12-15.sql.gz
         crm_orderField_2016-12-15.sql.gz
       crm_plan_2016-12-15.sql.gz

安裝環境說明

系統環境

[root@~]# cat /etc/redhat-release 
CentOS release 6.5 (Final)
[root@~]# uname -r
2.6.32-431.el6.x86_64

資料庫

• 由於是模擬環境，主從庫在同一臺伺服器上，伺服器IP地址192.168.1.7
• 主庫使用3306埠
• 從庫使用3307埠
• 資料庫資料目錄/data

安裝MySQL資料庫服務

下載軟體包

今天我們是用二進位制安裝包進行佈署MySQL資料庫服務，其它方式的安裝佈署方法請參考前面

[root@~]#wget http://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.51-linux2.6-x86_64.tar.gz 
建立資料目錄、軟體安裝目錄
[root@~]#mkdir /data｛3306，3307｝ -p
[root@~]#mkdri /application
解壓軟體
[root@~]#tar zxf mysql-5.5.51-linux2.6-x86_64.tar.gz 
[root@~]#mv mysql-5.5.51-linux2.6-x86_64 /application/mysql-5.5.51
[root@~]#ln -s /application/mysql-5.5.51 /application/mysql
建立使用者
[root@~]#groupadd mysql
[root@~]#useradd -g mysql -M mysql
初始化資料庫
[root@~]#/application/mysql/scripts/mysql_install_db --basedir=/application/mysql --datadir=/data/3306/data --user=mysql
[root@~]#/application/mysql/scripts/mysql_install_db --basedir=/application/mysql --datadir=/data/3307/data --user=mysql
建立配置檔案
[root@~]#vi /data/3306/my.cnf
[client]
port            = 3306
socket          = /data/3306/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
user    = mysql
port    = 3306
socket  = /data/3306/mysql.sock
basedir = /application/mysql
datadir = /data/3306/data
open_files_limit    = 1024
back_log = 600
max_connections = 800
max_connect_errors = 3000
table_cache = 614
external-locking = FALSE
max_allowed_packet =8M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100
thread_concurrency = 2
query_cache_size = 2M
query_cache_limit = 1M
query_cache_min_res_unit = 2k
thread_stack = 192K
tmp_table_size = 2M
max_heap_table_size = 2M
long_query_time = 1
pid-file = /data/3306/mysql.pid
log-bin = /data/3306/mysql-bin
           #主從同步的關鍵點，從庫上不需要開啟
relay-log = /data/3306/relay-bin
relay-log-info-file = /data/3306/relay-log.info
binlog_cache_size = 1M
max_binlog_cache_size = 1M
max_binlog_size = 2M
expire_logs_days = 7
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
lower_case_table_names = 1
skip-name-resolve
slave-skip-errors = 1032,1062
replicate-ignore-db=mysql
server-id = 1    #主庫從庫ID 不可相同
[mysqldump]
quick
max_allowed_packet = 2M
[mysqld_safe]
log-error=/data/3306/mysql3306.err
pid-file=/data/3306/mysqld.pid
資料庫啟動指令碼：
[root@~]#vi /data/3306/mysql
#!/bin/sh
port=3306
user="root"
pwd="123456"
Path="/application/mysql/bin"
sock="/data/${port}/mysql.sock"
start_mysql()
{
    if [ ! -e "$sock" ];then
      printf "Starting MySQL...\n"
      /bin/sh ${Path}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &
    else
      printf "MySQL is running...\n"
      exit
    fi
}
stop_mysql()
{
    if [ ! -e "$sock" ];then
       printf "MySQL is stopped...\n"
       exit
    else
       printf "Stoping MySQL...\n"
       ${Path}/mysqladmin -u ${user} -p${pwd} -S /data/${port}/mysql.sock shutdown
   fi
}
restart_mysql()
{
    printf "Restarting MySQL...\n"
    stop_mysql
    sleep 2
    start_mysql
}
case $1 in
start)
    start_mysql
;;
stop)
    stop_mysql
;;
restart)
    restart_mysql
;;
*)
    printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
esac
備註：主從庫配置檔案與啟動檔案一樣，只需修改埠與server-id即可完成配置
授權目錄並增加啟動檔案可執行許可權
[root@~]#chown -R mysql.mysql /data
[root@~]#find /data -name mysql -exex chmod +x {} \;
啟動資料庫
[root@~]#/data/3306/mysql start
[root@~]#/data/3307/mysql start
修改預設資料庫密碼
[root@~]#mysqladmin -uroot password '123456' -S /data/3306/mysql.sock
[root@~]#mysqladmin -uroot password '123456' -S /data/3307/mysql.sock
測試登陸，可以登陸兩個資料庫即可完成全部安裝過程

配置主庫

1）備份主庫

mkdir /backup

登陸主庫建立用步同戶並授權

[root@~]#mysql -uroot -p123456 -S /data/3306/mysql.sock
mysql> grant replication slave on *.* to rep@'192.168.1.%' identified by'123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

執行鎖表操作

[root@~]#/application/mysql/bin/mysql -uroot -p123456 -S /data/3306/mysql.sock -e "flush table with read lock;"

備份主庫

[root@~]#/application/mysql/bin/mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show master status;" >/backup/mysql.log
[root@~]#/application/mysql/bin/mysqldump -uroot -p123456 -S /data/3306/mysql.sock -A -B |gzip >/backup/mysql.sql.gz

解除鎖表狀態

[root@~]#/application/mysql/bin/mysql -uroot -p123456 -S /data/3306/mysql.sock -e "unlock tables;" 

備註：以上操作也可以登陸主庫進行，但是需要注意的是，執行鎖表操作後，需要另開啟一個視窗進行資料備份，不可直接退出，防止有資料寫入導致備份的資料不完整。最好是使用非互動式操作。

配置從庫實現主從同步

將主庫的備份檔案解壓並恢復資料庫

[root@backup ]#gzip -d mysql.sql.gz
[root@backup ]#/application/mysql/bin/mysql -uroot -p123456 -S /data/3307/mysql.sock < mysql.sql

檢視LOG日誌

[root@backup ]#cat mysql.log
+------------------+----------+--------------+-----------+
| File     | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------+----------+--------------+------------------+
| mysql-bin.000002 |      424 |              |             |
+----------+----------+--------------+------------------+

登陸從庫執行下面的操作

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.1.7',       #伺服器IP
    -> MASTER_PORT=3306,                     #主庫埠
    -> MASTER_USER='rep',                       #同步的使用者
    -> MASTER_PASSWORD='123456',     #同步的使用者密碼
    -> MASTER_LOG_FILE=' mysql-bin.000002',   #binlog檔案
    -> MASTER_LOG_POS=424;                   #位置點
mysql> start slave;             #開啟同步

等待60S後檢視同步狀態

[root@backup ]# mysql -S /data/3307/mysql.sock -e "show slave status\G"|egrep "Seconds_Behind_Master|_Running"
         Slave_IO_Running: Yes
         Slave_SQL_Running: Yes
         Seconds_Behind_Master: 0

只要出現上述情況說明主從同步成功

測試主從同步

主庫建立一個資料庫

[root@backup ~]# mysql -S /data/3306/mysql.sock -e "create database tongbuku"
[root@backup ~]# mysql -S /data/3306/mysql.sock -e "show databases"
+-----------------------------+
| Database                            |
+-----------------------------+
| information_schema         |
| mysql                                 |
| performance_schema       |
| test                                     |
| tongbuku                           |
+-----------------------------+

檢視從庫同步情況

[root@backup ~]# mysql -S /data/3307/mysql.sock -e "show databases"
+-----------------------------+
| Database                            |
+-----------------------------+
| information_schema         |
| mysql                                 |
| performance_schema       |
| test                                     |
| tongbuku                           |
+-----------------------------+

表明主從同步狀態正常，也可以在主庫新的資料表中建立表，再插入新的資料來測試主從同步狀態

mysql命令介紹

mysql 是資料庫管理命令
通過mysql --help來檢視相關引數及使用說明

mysql --help          
#mysql資料庫管理命令
Usage: mysql [OPTIONS] [database]    
           #語法格式
--help  #檢視幫助文件
--auto-rehash                            
           #自動補全功能
-A, --no-auto-rehash                
           #不需自動補全
-B, --batch                               
       #不使用歷史檔案，禁用互動
--character-sets-dir=name     
         #字符集安裝目錄
-C, --compress                 
#客戶端與服務端傳遞資訊時壓縮
-#--debug[=#]                        
         #呼叫功能
-D, --database=name    
         #使用資料庫
--default-character-set=name
        #設定預設字符集
-e, --execute=name             
         #執行sql語句
-E, --vertical                         
       #垂直列印輸出資訊
-f, --force                             
        #跳過錯誤，執行下面的命令
-G, --named-commands     
        #查詢結果按列列印
-i, --ignore-spaces              
        #忽略空格
-h, --host=name                
        #設定連線伺服器的地址與IP
--line-numbers                  
       #顯示有錯誤的行號
-L, --skip-line-numbers     
        #忽略有錯誤的行號
-n, --unbuffered                
        #每次執行sql後重新整理快取
--column-names               
        #查詢時顯示列資訊
-N, --skip-column-names  
        #不顯示列資訊
-p, --password[=name]     
        #輸入密碼資訊
-P, --port=#                       
       #設定埠資訊
    --prompt=name           
       #設定mysql提示符
    --protocol=name          
       #設定使用協議
-s, --silent                    
      #一行一行輸出，tab間隔
-S, --socket=name      
      #連線伺服器使用socket檔案
-t, --table                     
      #以表格的格式輸出
-u, --user=name            
      #連線伺服器的使用者名稱
-v, --verbose                   
     #列印sql執行的命令
-V, --version                   
     #輸出版本資訊
-w, --wait                     
     #伺服器停機後等待重啟的時間
--connect-timeout=#             
     #連線前要等待的時間
--max-allowed-packet=#          
#伺服器傳送與接收包的最大長度
--show-warnings                 
  #顯示警告資訊

mysqldump命令介紹

mysqldump資料備份命令（邏輯備份）
日常使用最為頻繁的命令之一，也是中小企業或者說資料量不大的情況下常用的資料庫備份命令，非常實用。

mysqldump --help          
#mysql資料庫備份命令（邏輯備份）
Usage: mysqldump [OPTIONS] database [tables]                   
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
mysqldump [OPTIONS] --all-databases [OPTIONS]
        #備份命令格式**
--print-defaults                
     #列印預設的程式引數列表
--no-defaults                    
   #不輸出預設選項引數
--defaults-file=#              
    #設定指定的選項引數檔案
-A, --all-databases           
    #所有資料庫
--add-drop-database       
#建立資料之前新增drop資料庫語句
--add-locks    
#每個表匯出之前增加lock tables並且之後unlock tables
--character-sets-dir           
#字符集檔案目錄
--compact
    #匯出更少的輸出資訊
-B --databases
#指定資料庫
--debug-info
#輸出除錯資訊並退出
--default-character-set  
#設定預設字符集，預設為utf8*
--dump-slave         
#將主binlog位置和檔名追加到匯出的資料檔案中
--events,-E   
   #備份事件資訊
--flush-logs,-F 
   #備份後重新整理日誌
-p, --password[=name] 
   #連線資料庫密碼
-P, --port=# 
   #設定埠資訊
-S, --socket=name 
   #連線伺服器使用socket檔案
-V, --version
    #輸出版本資訊
-u, --user=name 
   #連線伺服器的使用者名稱

mysqlbinlog命令介紹

mysqlbinlog 是用來檢視binlog二進位制日誌檔案資訊的命令，也是日常經常使用的命令之一，通常在恢復資料庫資料時使用。

mysqlbinlog --help              
#檢視mysql的binlog日誌檔案記錄的資訊
Usage: mysqlbinlog [options] log-files   
      #語法格式
--character-sets-dir=name        
        #指定字符集檔案目錄
-d, --database=name                
       #檢視指定資料庫的日誌檔案
-h, --host=name      
       #檢視指定主機上的日誌檔案
--start-position=953                  
            #起始pos點  
--stop-position=1437
           #結束pos點       
--start-datetime=    
        #起始時間點        
--stop-datetime=    
         #結束時間點    
--database=             
            #指定只恢復資料庫

建立使用者

mysql>create user test identified by 'BaC321@#';

修改密碼

5.5版本及以前的命令

mysql>set password for test=passowrd('!1A@2#3');  

5.6及以上命令

mysql>update mysql.user set authentication_string=password('A1b2c3#!@') where user='test';

建立使用者並授權

mysql>grant select,insert,update on student.* to test@localhost identified by 'A1b2c3#!@';

檢視授權

mysql> show grants for test@localhost;

移除許可權

mysql> revoke insert,update on student.* from test@localhost;

建庫與表

建立庫

mysql> create database student;
mysql> show databases;

建立表

mysql> use student;
mysql> create table T1 (name varchar(10) not null,sex varchar(10) not null);

通過現有的表建立新表

mysql> create table T2 as select * from T1;

插入資料

插入資料

mysql> insert into T1 values('zhang','man');
Query OK, 1 row affected (0.03 sec)
mysql> insert into T1 values('li','man');
Query OK, 1 row affected (0.03 sec)
mysql> insert into T1 values('wang','man');
Query OK, 1 row affected (0.02 sec)
mysql> insert into T1 values('zhao','women');
Query OK, 1 row affected (0.05 sec)

需要注意的是如果列超過兩列，就需要指定列欄位名如下

mysql> insert into T1(name,sex) values('gege','man');

查詢資料

mysql> select user,host from mysql.user;

檢視使用者

mysql> select * from T1 where name like '%an%';
mysql> select * from T1 where age like '2%';

匹配查詢

mysql> select * from T1 order by name,age;

查詢排序

mysql> select count(*) as toaolcount from T1;
mysql> select sum(age) as sumvalue from T1;
mysql> select avg(age) as avgvalue from T1;
mysql> select max(age) from T1;

查詢值

mysql> select score from T1 where score <91;
mysql> select score from T1 where score >=91;
mysql> select * from T1 where score in (96,100);

條件查詢

mysql> select * from T2;
mysql> select * from T1;

增刪更新

增加與刪除列

mysql> alter table T1 add age int(4) not null;
mysql> alter table T1 drop age

更新表裡的資料

mysql> update T1 set age=25 where name='zhang';
mysql> update T1 set age=23 where name='li';

刪除資料

mysql> delete from T1 where age='22';

建索引與刪除

mysql> create index indexT1 on T1(name(10));
mysql> drop index indexT1 on T1;

主鍵與檢視

建立主鍵

mysql> alter table T1 add primary key(name);
mysql> desc T1;

建立與刪除檢視

mysql> create view t1view as select name from T1;
mysql> select * from t1view;
mysql> drop view t1view;
mysql> select * from t1view;
ERROR 1146 (42S02): Table 'student.t1view' doesn't exist

提示此檢視不存在