MySQL 细致总结之基础篇

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

Linux系统MySQL数据库主从同步实战过程

安装环境说明

系统环境

[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数据库基础命令

创建用户

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

提示此视图不存在

本作品采用《CC 协议》,转载必须注明作者和本文链接
本帖由系统于 4年前 自动加精
讨论数量: 2

兄弟 写的很详细 不错的 :grin:

4年前 评论

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!