The foundation of MySQL detailed summary

Time:2021-1-15

Introduction to MySQL database — common basic commands

1. Using help information

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 # the simplest creation command
   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. Create, delete and view databases

mysql> create database test_data;
   #Create database of default character set (default is Latin character set)
Query OK, 1 row affected (0.02 sec)
mysql> show databases like "test%";
+------------------+
| Database (test%) |
+------------------+
| test_data        |
+------------------+
1 rows in set (0.00 sec)

Creating database of GBK character set

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; 

View the statement that created the database

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

Delete database

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

3. Connect to database

mysql> use test_gbk;

Equivalent to CD command, switch to the database for operation

Database changed
mysql> select database();

View the currently connected database, which is equivalent to PWD

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

To view the users currently connected to the database is equivalent to whoamI

+--------------------+
| user()               |
+-------------------+
| [email protected] |
+--------------------+
1 row in set (0.00 sec)

4. Creating users, authorizing and withdrawing permissions

When the database is created, users need to be created for the people who need to connect to the database to use and operate the database. It is impossible for everyone to log in with root, so the permission setting is also very important

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

Create a user and give all permissions to test_ All tables in GBK library, password ‘123456’

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

Refresh permissions to make them effective

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

See what permissions the user has

+-----------------------------------------------------------------------------------------------------------------+
| Grants for [email protected]                                                                                                  |
+-----------------------------------------------------------------------------------------------------------------+
| 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)

Withdraw authority

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

Withdraw the above permission

Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'testuser'@'localhost';                                       
+----------------------------------------------------------------------------------------------------+
| Grants for [email protected]                                                                          |
+----------------------------------------------------------------------------------------------------+
| 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)

Note: I didn’t know which permissions all permissions are. After using this method, it should be clear

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. Create and delete tables

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

Create a table and two fields

Query OK, 0 rows affected (0.06 sec)
MySQL > show tables; # view tables
+--------------------+
| Tables_in_test_gbk |
+--------------------+
| test                     |
+--------------------+
1 row in set (0.00 sec)
MySQL > desc test; # view table structure
+-------+----------+------+-----+---------+-------+
| 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)
Delete table
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)

View build table

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)

Multi instance configuration

1. What is multi instance

  • Multi instance is to open multiple different service ports (3306 by default) on a server and run multiple MySQL service processes. This service process monitors different service ports through different sockets to provide different services. All instances use a set of MySQL installation program together, but each uses different configuration files, startup programs, and data files It is relatively independent.
  • The main function of multi instance is to make full use of the existing server hardware resources to provide data services for different services, but if one instance has high concurrency, it will also affect the performance of other instances

2. Prepare for installing multi instance environment

Before installation, you need to install MySQL first, but you only need to make install (compile and install). If you use the installation free program, you just need to unzip the package. Today’s environment is to install the main program of MySQL through the installation free package (for other installations, please refer to the previous installation process for self-test)

System environment

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

erection sequence

mysql-5.5.52-linux2.6-x86_64.tar.gz
#First, download the software locally
wget http://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.52-linux2.6-x86_64.tar.gz

Create installation user

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

Create multi instance data directory

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

3. Install MySQL multi instance

Next, install multiple instances of MySQL

Decompression software

[[email protected] 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
[[email protected] tools]# tar zxf mysql-5.5.52-linux2.6-x86_64.tar.gz

Copy configuration file

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

To standardize the installation path, copy the installation free package to the application directory

[[email protected] tools]# mv mysql-5.5.52-linux2.6-x86_64 /application/mysql
[[email protected] 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

Modify configuration file and startup file

configuration file 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

Starting program file MySQL

[[email protected] 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

Other configurations can be modified by referring to the configuration file

Multi instance initialization

[[email protected] 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/

After successful initialization, a data directory data and some files will be generated under the data directory

[[email protected] 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

For the initialization of another instance, please refer to the above operation

[[email protected] 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. Start multiple instances and log in

Start the service

[[email protected] 3307]# /data/3306/mysql start
Starting MySQL...
[[email protected] 3307]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 19986 mysql 10u IPv4 90967 0t0 TCP *:mysql (LISTEN)
[[email protected] 3307]# /data/3307/mysql
start Starting MySQL...
[[email protected] 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)

Check port

[[email protected] 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

Log in to multi instance database

[[email protected] ~]# 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
[[email protected] ~]# 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)

Login successfully, and create database in 3306 instance, but view the data that has not been created on 3307 instance, indicating that the two instances are independent

Note: if you need to add another instance, the basic configuration steps are the same as above. You only need to modify the port number and the path of the data directory in the configuration file and startup program file. Finally, you can add the multi instance database startup command to the boot self startup

Backup database

Let’s first look at the data in the database

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. Single database backup

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

Now let’s see what the backup file is

[[email protected] ~]# 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;

From the file content on, we can see that the actual backup process is to back up the SQL statements of creating database, creating table and inserting data, or to export the SQL statements

-Parameter B

[[email protected] ~]# mysqldump -uroot -p -B test >/download/testbak_$(date +%F)_b.sql
Enter password: 
[[email protected] ~]# egrep -v "^--|^$" /download/testbak_2016-12-12_b.sql 
/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
/*!40101 SET @[email protected]@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @[email protected]@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @[email protected]@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @[email protected]@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 [email protected]_TIME_ZONE */;
/*!40101 SET [email protected]_SQL_MODE */;
/*!40014 SET [email protected]_FOREIGN_KEY_CHECKS */;
/*!40014 SET [email protected]_UNIQUE_CHECKS */;
/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;
/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;
/*!40101 SET [email protected]_COLLATION_CONNECTION */;
/*!40111 SET [email protected]_SQL_NOTES */;

-The function of parameter B is clear at a glance, that is, when our database is lost, we can directly use this backup file for recovery, and there is no need to rebuild the database and table, and then carry out data recovery operation

2. Compressed backup

Sometimes, the data of the database is relatively large, and it may be used for backup after compression to save backup time and disk space

[[email protected] ~]# mysqldump -uroot -p -B test|gzip >/download/testbak_$(date +%F).sql.gz
Enter password: 
[[email protected] ~]# 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
[[email protected] ~]# 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

At the same time, we can also see the effect of compression

3. Multi database backup

[[email protected] ~]# 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.
[[email protected] ~]# 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

Here is a warning message, which can be ignored or added with parameters during backup. The backup statement is as follows

[[email protected] ~]# ****mysqldump -uroot -p -B --events test mysql|gzip >/download/testbak_$(date +%F).sql02.gz
Enter password: 
[[email protected] ~]# 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
So there won't be a warning message

However, this kind of multi database backup will cause a problem. If only one of the databases has a problem, it is not easy to restore a single database. Therefore, this backup method is not commonly used and does not meet the actual needs. Therefore, multiple single database backup operations are required during multi database backup

[[email protected] ~]# mysqldump -uroot -p -B test|gzip >/download/testbackup_$(date +%F).sql.gz                 
Enter password: 
[[email protected] ~]# mysqldump -uroot -p -B --events mysql|gzip >/download/mysqlbak_$(date +%F).sql.gz        
Enter password: 
[[email protected] ~]# 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. Single table backup

Sub database backup is to facilitate the operation of database recovery, but it also faces problems. If a table in a certain database is damaged, but the whole database is not recovered, so the sub database and sub table backup are commonly used in the actual production, so that the data is also backed up, and the recovery is easy to operate

[[email protected] ~]# mysqldump -uroot -p -B test test >/download/test_testbak_$(date +%F).sql      
Enter password: 
[[email protected] ~]# 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

Therefore, the backup of separate tables is the same as that of separate databases. It only needs multiple single table backup operations. However, some small partners will definitely ask questions. If there are thousands of tables or tens of thousands of tables in a database, this kind of backup will last for a long time???? Professional backup tools can be used for backup with large amount of data. In case of small amount of data or not many tables, the backup operation can be written as a script and included in the scheduled task for regular execution. You only need to check whether the backup is successful

A simple backup script in the actual production environment, for reference only

[[email protected] 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}'`
 #Define server IP variables
BAKDIR=/backup  
  #Define backup path
[ ! -d $BAKDIR/${ip} ] && mkdir -p $BAKDIR/${ip}
 #Judge if this path does not exist, create one for the convenience of seeing when there are many servers
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'`
  #Define database variables
for name in $DB_NAME
#The for loop takes the library name
do
  $MYSQL_DUMP -u$DB_USER -p$DB_PWD -B ${name} |gzip >$BAKDIR/${ip}/${name}_$DATA.sql.gz  
 #Full database backup
  [ ! -d $BAKDIR/${ip}/${name} ] && mkdir -p  $BAKDIR/${ip}/${name}
#Judge this path, in order to distinguish which library's backup file
  for tablename in `$MYSQL -u$DB_USER -p$DB_PWD -e "show tables from ${name};"|sed '1d'`
#For loop statement takes table name
  do
   $MYSQL_DUMP -u$DB_USER -p$DB_PWD ${name} ${tablename} |gzip >$BAKDIR/${ip}/${name}/${tablename}_$DATA.sql.gz
#Sub table backup
  done
done

The results of implementation are as follows

[[email protected] ~]# tree /backup/
/backup/
10.1xx.1xx.1xx server IP
  XXXXXXXX is actually the name of the library
      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

Master slave synchronization process of MySQL database in Linux system

Installation environment description

System environment

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

database

  • Because it is a simulation environment, the master and slave libraries are on the same server, and the server IP address is 192.168.1.7
  • The main library uses port 3306
  • Using port 3307 from the library
  • Database data directory / data

Install MySQL database service

Download package

Today, we deploy MySQL database service with binary installation package. Please refer to the previous section for other installation and deployment methods

[[email protected]~]#wget http://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.51-linux2.6-x86_64.tar.gz 
Create data directory and software installation directory
[[email protected]~]#mkdir /data{3306,3307} -p
[[email protected]~]#mkdri /application
Decompression software
[[email protected]~]#tar zxf mysql-5.5.51-linux2.6-x86_64.tar.gz 
[[email protected]~]#mv mysql-5.5.51-linux2.6-x86_64 /application/mysql-5.5.51
[[email protected]~]#ln -s /application/mysql-5.5.51 /application/mysql
Create user
[[email protected]~]#groupadd mysql
[[email protected]~]#useradd -g mysql -M mysql
Initialize database
[[email protected]~]#/application/mysql/scripts/mysql_install_db --basedir=/application/mysql --datadir=/data/3306/data --user=mysql
[[email protected]~]#/application/mysql/scripts/mysql_install_db --basedir=/application/mysql --datadir=/data/3307/data --user=mysql
create profile
[[email protected]~]#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
           #The key point of master-slave synchronization does not need to be opened on the slave library
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 ᦇ the ID of master and slave libraries cannot be the same
[mysqldump]
quick
max_allowed_packet = 2M
[mysqld_safe]
log-error=/data/3306/mysql3306.err
pid-file=/data/3306/mysqld.pid
Database startup script:
[[email protected]~]#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
Note: the master-slave configuration file is the same as the startup file. You only need to modify the port and server ID to complete the configuration
Authorize the directory and increase the executable permission of startup file
[[email protected]~]#chown -R mysql.mysql /data
[[email protected]~]#find /data -name mysql -exex chmod +x {} \;
Start database
[[email protected]~]#/data/3306/mysql start
[[email protected]~]#/data/3307/mysql start
Modify default database password
[[email protected]~]#mysqladmin -uroot password '123456' -S /data/3306/mysql.sock
[[email protected]~]#mysqladmin -uroot password '123456' -S /data/3307/mysql.sock
Test login, you can login two databases to complete the installation process

Configure main library

1) Backup main library

mkdir /backup

Log in to the main database, create the same user and authorize

[[email protected]~]#mysql -uroot -p123456 -S /data/3306/mysql.sock
mysql> grant replication slave on *.* to [email protected]'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)

Execute lock table operation

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

Backup main library

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

Unlock table status

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

Note: the above operations can also be performed in the main database, but it should be noted that after the operation of locking the table, another window needs to be opened for data backup. You can’t exit directly to prevent incomplete backup data caused by data writing. It is best to use non interactive operations.

Configure slave library to realize master-slave synchronization

Decompress the backup file of the main library and restore the database

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

View log log

[[email protected] ]#cat mysql.log
+------------------+----------+--------------+-----------+
| File     | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------+----------+--------------+------------------+
| mysql-bin.000002 |      424 |              |             |
+----------+----------+--------------+------------------+

Log in from the library and do the following

mysql> CHANGE MASTER TO
    -> MASTER_ Host ='192.168.1.7 ', # server IP
    -> MASTER_ Port = 3306, # main library port
    -> MASTER_ User ='rep ', ා synchronized user
    -> MASTER_ Password = - 123456 ', # synchronized user password
    -> MASTER_ LOG_ File ='mysql-bin.000002 ', binlog file
    -> MASTER_ LOG_ POS = 424; # location point
MySQL > start slave; # start synchronization

Wait for 60s to check the synchronization status

[[email protected] ]# 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

As long as the above situation occurs, the master-slave synchronization is successful

Test master slave synchronization

The main library creates a database

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

View the synchronization of slave Libraries

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

It indicates that the master-slave synchronization state is normal. You can also create a table in the new data table of the master database and insert new data to test the master-slave synchronization state

Introduction to common commands

Introduction to MySQL command

MySQL is a database management command
Through MySQL — help to view the relevant parameters and instructions

mysql --help          
#Mysql database management command
Usage: mysql [OPTIONS] [database]    
           #Grammatical format
--Help # view help documents
--auto-rehash                            
           #Automatic completion function
-A, --no-auto-rehash                
           #No need for automatic completion
-B, --batch                               
       #Do not use history file, disable interaction
--character-sets-dir=name     
         #Character set installation directory
-C, --compress                 
#Compression when client and server transmit information
-#--debug[=#]                        
         #Call function
-D, --database=name    
         #Using the database
--default-character-set=name
        #Set default character set
-e, --execute=name             
         #Execute SQL statement
-E, --vertical                         
       #Vertical printout information
-f, --force                             
        #Skip the error and execute the following command
-G, --named-commands     
        #Query results are printed by column
-i, --ignore-spaces              
        #Ignore spaces
-h, --host=name                
        #Set the address and IP of the connection server
--line-numbers                  
       #Show wrong line number
-L, --skip-line-numbers     
        #Ignore line numbers with errors
-n, --unbuffered                
        #Refresh the cache after each SQL execution
--column-names               
        #Display column information when querying
-N, --skip-column-names  
        #Do not display column information
-p, --password[=name]     
        #Enter password information
-P, --port=#                       
       #Set port information
    --prompt=name           
       #Set MySQL prompt
    --protocol=name          
       #Set usage protocol
-s, --silent                    
      #Output line by line, tab interval
-S, --socket=name      
      #Using socket file to connect to server
-t, --table                     
      #Output in tabular format
-u, --user=name            
      #The user name to connect to the server
-v, --verbose                   
     #Print commands executed by SQL
-V, --version                   
     #Output version information
-w, --wait                     
     #Time to wait for restart after server shutdown
--connect-timeout=#             
     #Time to wait before connecting
--max-allowed-packet=#          
#The maximum length of packets sent and received by the server
--show-warnings                 
  #Display warning message

Introduction of mysqldump command

Mysqldump data backup command (logical backup)
One of the most frequently used commands in daily life is also a common database backup command for small and medium-sized enterprises or small amount of data, which is very practical.

mysqldump --help          
#Mysql database backup command (logical backup)
Usage: mysqldump [OPTIONS] database [tables]                   
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
mysqldump [OPTIONS] --all-databases [OPTIONS]
        #Backup command format**
--print-defaults                
     #Print default program parameter list
--no-defaults                    
   #Do not output default option parameters
--defaults-file=#              
    #Sets the specified options parameter file
-A, --all-databases           
    #All databases
--add-drop-database       
#Add drop database statement before creating data
--add-locks    
#Add lock tables before each table export and unlock tables after each table export
--character-sets-dir           
#Character set file directory
--compact
    #Export less output information
-B --databases
#Specify the database
--debug-info
#Output debug information and exit
--default-character-set  
#Set the default character set, the default is utf8*
--dump-slave         
#Append the main binlog location and file name to the exported data file
--events,-E   
   #Backup event information
--flush-logs,-F 
   #Refresh log after backup
-p, --password[=name] 
   #Connection database password
-P, --port=# 
   #Set port information
-S, --socket=name 
   #Using socket file to connect to server
-V, --version
    #Output version information
-u, --user=name 
   #The user name to connect to the server

Introduction to mysqlbinlog command

Mysqlbinlog is a command used to view the binary log file information of binlog. It is also one of the commands often used in daily life. It is usually used when restoring database data.

mysqlbinlog --help              
#View the information recorded in the binlog log file of MySQL
Usage: mysqlbinlog [options] log-files   
      #Grammatical format
--character-sets-dir=name        
        #Specifies the character set file directory
-d, --database=name                
       #View the log file of the specified database
-h, --host=name      
       #View the log files on the specified host
--start-position=953                  
            #Starting POS point  
--stop-position=1437
           #End POS point       
--start-datetime=    
        #Starting time point        
--stop-datetime=    
         #End time point    
--database=             
            #Specifies that only the database is recovered

Mysql database basic command

Create user

mysql>create user test identified by '[email protected]#';

Change Password

Version 5.5 and previous commands

mysql>set password for test=passowrd('[email protected]#3');  

5.6 and above orders

mysql>update mysql.user set authentication_string=password('A1b2c3#[email protected]') where user='test';

Create user and authorize

mysql>grant select,insert,update on student.* to [email protected] identified by 'A1b2c3#[email protected]';

View authorization

mysql> show grants for [email protected];

Remove permissions

mysql> revoke insert,update on student.* from [email protected];

Database and table construction

Create Library

mysql> create database student;
mysql> show databases;

Create table

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

Create a new table from an existing table

mysql> create table T2 as select * from T1;

insert data

insert data

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)

Note that if there are more than two columns, you need to specify the column field name as follows

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

Query data

mysql> select user,host from mysql.user;

View users

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

Match query

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

Query sort

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;

Query value

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);

Conditional query

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

Add, delete and update

Add and delete columns

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

Update the data in the table

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

Delete data

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

Indexing and deleting

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

Primary key and view

Create primary key

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

Creating and deleting views

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

Prompt that this view does not exist

This work adoptsCC agreementReprint must indicate the author and the link of this article

Recommended Today

020_CSS3

catalog How to learn CSS What is CSS History of development quick get start Advantages of CSS Three ways to import CSS Expansion: two ways of writing external style selector Basic selector Hierarchy selector Structure pseudo class selector attribute selectors Beautify web page elements Why beautify web pages Span label: for the text that needs […]