备份和恢复

mysqldump — A Database Backup Program

refer to : https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html
https://dev.mysql.com/doc/refman/8.0/en/using-mysqldump.html

The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data.

It dumps one or more MySQL databases for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format.

Invocation Syntax

There are in general three ways to use mysqldump—in order to dump a set of one or more tables, a set of one or more complete databases, or an entire MySQL server—as shown here:

mysqldump [options] db_name [tbl_name ...]
mysqldump [options] --databases db_name ...
mysqldump [options] --all-databases

To dump entire databases, do not name any tables following db_name, or use the --databases or --all-databases option.

To see a list of the options your version of mysqldump supports, issue the command mysqldump --help.

Option Syntax - Alphabetical Summary

mysqldump supports the following options, which can be specified on the command line or in the [mysqldump] and [client] groups of an option file. For information about option files used by MySQL programs, see Section 4.2.2.2, “Using Option Files”.

Table 4.14 mysqldump Options

Connection Options

The mysqldump command logs into a MySQL server to extract information. The following options specify how to connect to the MySQL server, either on the same machine or a remote system.

–host=host_name, -h host_name
Dump data from the MySQL server on the given host. The default host is localhost.

–password[=password], -p[password]

The password of the MySQL account used for connecting to the server. The password value is optional. If not given, mysqldump prompts for one. If given, there must be no space between --password= or -p and the password following it. If no password option is specified, the default is to send no password.

Specifying a password on the command line should be considered insecure. To avoid giving the password on the command line, use an option file. See Section 6.1.2.1, “End-User Guidelines for Password Security”.

To explicitly specify that there is no password and that mysqldump should not prompt for one, use the --skip-password option.

–port=port_num, -P port_num
For TCP/IP connections, the port number to use.

Restrictions

mysqldump does not dump the performance_schema or sys schema by default. To dump any of these, name them explicitly on the command line. You can also name them with the --databases option. For performance_schema, also use the --skip-lock-tables option.

mysqldump does not dump the INFORMATION_SCHEMA schema.

mysqldump does not dump InnoDB CREATE TABLESPACE statements.

mysqldump does not dump the NDB Cluster ndbinfo information database.

mysqldump includes statements to recreate the general_log and slow_query_log tables for dumps of the mysql database. Log table contents are not dumped.

Dumping Data in SQL Format with mysqldump

This section describes how to use mysqldump to create SQL-format dump files. For information about reloading such dump files, see Section 7.4.2, “Reloading SQL-Format Backups”.

By default, mysqldump writes information as SQL statements to the standard output. You can save the output in a file:

$> mysqldump [arguments] > file_name

To dump all databases, invoke mysqldump with the --all-databases option:

$> mysqldump --all-databases > dump.sql

To dump only specific databases, name them on the command line and use the --databases option:

$> mysqldump --databases db1 db2 db3 > dump.sql

The --databases option causes all names on the command line to be treated as database names. Without this option, mysqldump treats the first name as a database name and those following as table names.

With --all-databases or --databases, mysqldump writes CREATE DATABASE and USE statements prior to the dump output for each database.

This ensures that when the dump file is reloaded, it creates each database if it does not exist and makes it the default database so database contents are loaded into the same database from which they came.

If you want to cause the dump file to force a drop of each database before recreating it, use the --add-drop-database option as well. In this case, mysqldump writes a DROP DATABASE statement preceding each CREATE DATABASE statement.

To dump a single database, name it on the command line:

$> mysqldump --databases test > dump.sql

In the single-database case, it is permissible to omit the --databases option:

$> mysqldump test > dump.sql

The difference between the two preceding commands is that without --databases, the dump output contains no CREATE DATABASE or USE statements. This has several implications:

When you reload the dump file, you must specify a default database name so that the server knows which database to reload.

For reloading, you can specify a database name different from the original name, which enables you to reload the data into a different database.

If the database to be reloaded does not exist, you must create it first.

Because the output contains no CREATE DATABASE statement, the --add-drop-database option has no effect. If you use it, it produces no DROP DATABASE statement.

To dump only specific tables from a database, name them on the command line following the database name:

$> mysqldump test t1 t3 t7 > dump.sql

Recovery

Reloading SQL-Format Backups

To reload a dump file written by mysqldump that consists of SQL statements, use it as input to the mysql client.

If the dump file was created by mysqldump with the --all-databases or --databases option, it contains CREATE DATABASE and USE statements and it is not necessary to specify a default database into which to load the data:

$> mysql < dump.sql

Alternatively, from within mysql, use a source command:

mysql> source dump.sql

If the file is a single-database dump not containing CREATE DATABASE and USE statements, create the database first (if necessary):

$> mysqladmin create db1

Then specify the database name when you load the dump file:

$> mysql db1 < dump.sql

Alternatively, from within mysql, create the database, select it as the default database, and load the dump file:

mysql> CREATE DATABASE IF NOT EXISTS db1;
mysql> USE db1;
mysql> source dump.sql

备份和恢复，实战记录

如果源库中，存在函数，那么备份时，mysqldump需要添加--routines参数。
https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html

[root@VM-0-14-opencloudos ~]# whereis mysqldump
mysqldump: /usr/bin/mysqldump /usr/share/man/man1/mysqldump.1.gz
[root@VM-0-14-opencloudos ~]# cd /usr/bin/
[root@VM-0-14-opencloudos bin]# ls mysql*
mysql       mysqlbinlog  mysql_config_editor  mysqldump      mysqlimport  mysql_secure_installation  mysqlslap            mysql_tzinfo_to_sql
mysqladmin  mysqlcheck   mysqld_pre_systemd   mysqldumpslow  mysqlpump    mysqlshow                  mysql_ssl_rsa_setup  mysql_upgrade



[root@VM-0-14-opencloudos bin]# mysqldump --host=47.118.54.238 --port=3307 --user=root --password=mysql2017 --databases iot > /opt/Mysql/Backup/iot.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

[root@VM-0-14-opencloudos bin]# 
[root@VM-0-14-opencloudos bin]# 
[root@VM-0-14-opencloudos bin]# 
[root@VM-0-14-opencloudos bin]# cd /opt/Mysql/Backup
[root@VM-0-14-opencloudos Backup]# ls
iot.sql

[root@VM-0-14-opencloudos bin]# mysql --host=localhost --user=root --password
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1727
Server version: 8.0.17 MySQL Community Server - GPL

Copyright (c) 2000, 2019, 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> source /opt/Mysql/Backup/iot.sql
Query OK, 0 rows affected (0.00 sec)

定时备份

创建一个脚本文件mysql_dump.sh

vim mysql_dump.sh

内容如下：

#!/bin/bash

#保存备份个数，备份7天数据
number=7
#备份保存路径
backup_dir=/opt/MysqlBackup
#日期
dd=`date +%Y-%m-%d-%H-%M-%S`
#备份工具
tool=mysqldump
#用户名
mysql_username=root
#密码
mysql_password=Mysql@2017
#将要备份的数据库
database_name=iot_config

#如果文件夹不存在则创建
if [ ! -d $backup_dir ];
then     
    mkdir -p $backup_dir;
fi

#简单写法 mysqldump -u root -p123456 users > /root/mysqlbackup/users-$filename.sq

mysqldump --user=${mysql_username} --password=${mysql_password} --routines --databases ${database_name} > ${backup_dir}/${database_name}-${dd}.sql


#写创建备份日志
echo "create $backup_dir/$database_name-$dd.dupm" >> $backup_dir/log.txt

#找出需要删除的备份
delfile=`ls -l -crt $backup_dir/*.sql | grep -v total | awk '{print $9 }' | head -1`

#判断现在的备份数量是否大于$number
count=`ls -l -crt $backup_dir/*.sql | grep -v total | awk '{print $9 }' | wc -l`

if [ $count -gt $number ]
then
  #删除最早生成的备份，只保留number数量的备份
  rm $delfile
  #写删除文件日志
  echo "delete $delfile" >> $backup_dir/log.txt
fi

给脚本，添加执行权限

chmod u+x mysql_dump.sh

先测试上面的脚本，是否能正常执行，如果能正常执行，下面新增一个定时任务

crontab -e

# 每隔1分钟执行一次脚本
*/1 * * * * /opt/MysqlBackup/mysql_dump.sh

Mysql—The MySQL Command-Line Client

refer to : https://dev.mysql.com/doc/refman/8.0/en/mysql.html

mysql is a simple SQL shell with input line editing capabilities.

It supports interactive and noninteractive use. When used interactively, query results are presented in an ASCII-table format. When used noninteractively (for example, as a filter), the result is presented in tab-separated format. The output format can be changed using command options.

Using mysql is very easy. Invoke it from the prompt of your command interpreter as follows:

mysql db_name

Or:

mysql --user=user_name --password db_name

In this case, you’ll need to enter your password in response to the prompt that mysql displays:

Enter password: your_password
Then type an SQL statement, end it with ;, \g, or \G and press Enter.

Typing Control+C interrupts the current statement if there is one, or cancels any partial input line otherwise.

mysql Client Options

refer to: https://dev.mysql.com/doc/refman/8.0/en/mysql-command-options.html

Table 4.11 mysql Client Options

mysql Client Commands

refer to : https://dev.mysql.com/doc/refman/8.0/en/mysql-commands.html

mysql sends each SQL statement that you issue to the server to be executed. There is also a set of commands that mysql itself interprets. For a list of these commands, type help or \h at the mysql> prompt:

mysql> help

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given
               outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing
               binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
resetconnection(\x) Clean session context.
query_attributes Sets string parameters (name1 value1 name2 value2 ...)
for the next query to pick up.
ssl_session_data_print Serializes the current SSL session data to stdout 
or file.

For server side help, type 'help contents'

Mysql使用FAQ

select中定义的alias，能用于哪些地方

refer to : https://dev.mysql.com/doc/refman/8.0/en/problems-with-alias.html

An alias can be used in a query select list to give a column a different name. You can use the alias in GROUP BY, ORDER BY, or HAVING clauses to refer to the column:

SELECT SQRT(a*b) AS root FROM tbl_name
  GROUP BY root HAVING root > 0;
SELECT id, COUNT(*) AS cnt FROM tbl_name
  GROUP BY id HAVING cnt > 0;
SELECT id AS 'Customer identity' FROM tbl_name;

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined. For example, the following query is illegal:

SELECT id, COUNT(*) AS cnt FROM tbl_name
  WHERE cnt > 0 GROUP BY id;

工作中，常将select定义的别名，用于group by中。如下：

SELECT DATE_FORMAT(trigger_time,'%Y-%m-%d') as everyday FROM iot.t_alarm_record group by everyday;

Alter

refer to :https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

语法:

ALTER TABLE tbl_name
    [alter_option [, alter_option] ...]
    [partition_options]

alter_option: {
    table_options
  | ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | ADD [COLUMN] (col_name column_definition,...)
  | ADD {INDEX | KEY} [index_name]
        [index_type] (key_part,...) [index_option] ...
  | ADD {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name]
        (key_part,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY
        [index_type] (key_part,...)
        [index_option] ...
  | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
        [index_name] [index_type] (key_part,...)
        [index_option] ...

usage：

ALTER TABLE res_product_group MODIFY COLUMN res_id  BIGINT UNSIGNED;


ALTER TABLE promo_info ADD COLUMN automatic  tinyint(1) NOT NULL; 


alter table ICoupon add extra VARCHAR(255);


ALTER TABLE icoupon ADD COLUMN excludeMarkets varchar(500) DEFAULT NULL;

ALTER TABLE promo_info DROP COLUMN limit_repeat_count;

ALTER TABLE promo_res MODIFY COLUMN is_required  tinyint(1) default 1;

alter table alarm_record_statistics add constraint union_pri_key primary key(alarm_day,fboxno,trigger_status,tenant_id);