Mysql5.7.20 Installation Documentation

Mysql5.7.20 Installation Documentation

I. Recovery and backup of databases under Linxu

Backup is extremely important when we keep important data in our MySQL database. This article describes how to use mysqldump to back up and recover data, which can be used to back up data in a database into a single text file or to migrate a backed-up database to another server. (The process is followed by the actual operation)

(1) Backing up the MySQL database from the command line

In the Linux operating system, we can use the mysqldump command for backup. The command connects to the MySQL server and creates a SQL dump file that contains all the SQL statements necessary to recreate the database. The basic syntax for this command is as follows:

$ mysqldump--opt-u [uname]-p[pass] [dbname] > [Backupfile.sql]

[uname] Database user name

[Pass] Database password

[dbname] Database name

[Backupfile.sql] Database backup file name

[--opt] mysqldump options

For example: To back up a database tuixin to a 20131112.sql file, you can simply use the following command:

$ mysqldump-u root-p tuixin > 20131112.sql

If it is necessary to export all databases at once, use the--all-databases option with the following command:

$ mysqldump-u root-p--all-databases > 20131112.sql

If the MySQL database is large, you may want to compress the mysqldump exported SQL file, and you can export the gzip file by using only the following backup commands and connecting to gzip through the pipeline.

$ mysqldump-u Root-p Tuixin | gzip-9 > 20131112.sql.gz

The decompression method is as follows:

$ gunzip 20131112.sql.gz

(2) Restore MySQL database from the command line

With the above method, we have backed up the database tuixin to a 20131112.sql file, and then we restore the Tuixin database:

* Create the appropriate database name on the target machine

* Load SQL file using MySQL command

$ mysql-u [uname]-p[pass] [Db_to_restore] < [Backupfile.sql]

For example, we now restore 20131112.sql files to the Tuixin database:

$ mysql-u root-p Tuixin < 20131112.sql

If you are recovering a compressed backup file, you can follow the following format:

$ gunzip < [backupfile.sql.gz] | Mysql-u [uname]-p[pass] [dbname]

If you are restoring a database that already exists, you can use the Mysqlimport command, which has the following syntax format:

$ mysqlimport-u [uname]-p[pass] [dbname] [Backupfile.sql]

Actual procedure:

Backup:

Use Mysqldump's-all-databases parameter to export all the databases under your database root to a SQL file in one breath. Then, after reloading the system, use the source command to rewind back in a breath.

Export all databases Mysqldump-uroot-p--all-databases > Sqlfile.sql

This will lead to all databases under the root user of the database server. If you are prompted after enter Password: Please enter your MySQL root password.

Import:

1. Login Mysal:

Mysql–u root–p

Enter the password as prompted

And then:

The path to the Source backup file;

Remember, there's a semicolon behind this sentence.

Second, the download of MySQL5.7.20

First, to the official website to download it:

https://www.mysql.com/downloads/

Three, decompression and installation:

#tar-XVF Mysql-5.7.18-1.el6.x86_64.rpm-bundle.tar

The following RPMs are available after decompression:

Installation method One:

CD to unpacked directory

Yum-y Localinstall mysql*

Installation Method Two:

#rpm-IVH mysql-community-server-5.7.18-1.el7.x86_64.rpm

You can use this one package to install a package, but I do not recommend.

Four, start and set: 1, start

CENTOS6 Start-up method:

# service Mysqld Start

View status

# Service MYSQLD Status

Centos7 Start-up method:

# Systemctl Start mysqld

View Status:

# SYSTEMCTL Status Mysqld

2. The settings involved for the first launch

After this method is installed, it will automatically generate a password in the my.cnf file, perform a look at it:

# Cat/var/log/mysqld.log | grep password

2017-05-13t05:39:44.497086z 1 [Note] A temporary password is generated for [email protected]: pkso:jin<4f%

The password is so complex, copy it, or lose the hand is also prone to error.

Login: #mysql-u root-p

Enter password:pkso:jin<4f%

The landing was successful, but at this time, nothing could be done. Whatever you do will prompt you:

ERROR 1820 (HY000): Must reset your passwordusing ALTER USER statement before executing this statement.

then reset it:

mysql> SET PASSWORD = PASSWORD (' Mysql5720 ');

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

(Password is too simple, must be uppercase, lowercase, numbers, symbols)

mysql> SET PASSWORD = PASSWORD ('mysql5720%');

Query OK, 0 rows affected, 1 Warning (0.00 sec)

CREATE USER ' root ' @ '% ' identified by ' [email protected] ';

CREATE USER ' root ' @ ' localhost ' identified by ' [email protected] ';

GRANT all privileges on * * to ' root ' @ '% ' identified by ' [email protected] ';

GRANT all privileges on * * to ' root ' @ ' localhost ' identified by ' [email protected] ';

Flush privileges;

OK, now it's ready to operate:

mysql> CREATE DATABASE Roger;

Query OK, 1 row affected (0.01 sec)

Mysql> use Roger;

Database changed

Mysql>

Extra: General self-installed MySQL is used to test, the result password is still so troublesome, which day forget all possible, want to set a simple password, the password security level set Low:

Mysql> set global validate_password_policy=0; 0 minimum, 1 Intermediate, 2 strongest authentication Level

Query OK, 0 rows Affected (0.00 sec)

Five, authorized remote connection:

Mysql>grant all privileges on * * to ' root ' @ '% ' identified by 'mysql5720%' with GRANT OPTION;

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> FLUSH privileges;

Query OK, 0 rows affected (0.01 sec)

In this way, we can remotely connect the MySQL database on Linux to the MySQL client on other computers: (for example, in win cmd)

C:\Program Files (x86) \mysql\mysql Server 5.0\bin>mysql-u root-pmysql5718%-H 192.168.231.8-d Roger

Vi. Expansion: MySQL5.7.20 password settings modify error 1819 (HY000): Your password does not satisfy the current policy requirements

To enhance security, MySQL5.7 randomly generates a password for the root user, in error log, about the location of the error log and, if the RPM package is installed, the default is/var/log/mysqld.log.

Generally available through Log_error settings

@ @log_error;

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

@ @log_error |

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

| /var/log/mysqld.log |

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

1 row in Set (0.00 sec)

MySQL's temporary password can be obtained via the # grep "password"/var/log/mysqld.log command

-19t05:1 [Note] A temporary password is generated for [email Protected]: Waq,qr%be2 (5

After you log on to the server with this password, you must change the password immediately, or you will receive the following error:

Mysql> Select User ();

1820 (HY000): Must reset your password using ALTER USER statement before executing this statement.

If you just change to a simple password, the following error will be reported:

mysql> ALTER User User () identified by ' 12345678 ';

1819 (HY000): Your password does not satisfy the current policy requirements

This is actually related to the value of Validate_password_policy.

The validate_password_policy has the following values:

Policy	Tests performed
0OrLOW	Length
1OrMEDIUM	Length; Numeric, lowercase/uppercase, and special characters
2OrSTRONG	Length; Numeric, lowercase/uppercase, and special characters; Dictionary file

The default is 1, which is medium, so the password you just started must match the length and must contain numbers, lowercase or uppercase letters, special characters.

Sometimes, just for your own testing, don't want to set the password so complicated, for example, I just want to set the root password is 123456.

Two global parameters must be modified:

First, modify the value of the Validate_password_policy parameter

Mysql> set global validate_password_policy=0;

0 rows affected (0.00 sec)

In this way, the criteria for judging the password is based on the length of the password. This is determined by the Validate_password_length parameter.

@ @validate_password_length;

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

@ @validate_password_length |

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

8 |

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

1 row in Set (0.00 sec)

The Validate_password_length parameter defaults to 8, and it has the minimum limit, and the minimum value is:

Validate_password_number_count

+ Validate_password_special_char_count

+ (2 * validate_password_mixed_case_count)

Where validate_password_number_count specifies the length of the data in the password, validate_password_special_char_count specifies the length of the special characters in the password, Validate_ PASSWORD_MIXED_CASE_COUNT Specifies the length of the size letter in the password.

These parameters, the default value is 1, so the Validate_password_length minimum value is 4, if you explicitly specify that the value of validate_password_length is less than 4, although there is no error, but Validate_password_ The value of length will be set to 4. As shown below:

@ @validate_password_length;

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

@ @validate_password_length |

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

| 8 |

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

1 row in Set (0.00 sec)

Mysql> set global validate_password_length=1;

0 rows affected (0.00 sec)

@ @validate_password_length;

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

@ @validate_password_length |

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

4 |

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

1 row in Set (0.00 sec)

If you modify the Validate_password_number_count,validate_password_special_char_count,validate_password_mixed_case_ Any value in count, Validate_password_length is dynamically modified.

@ @validate_password_length;

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

@ @validate_password_length |

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

4 |

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

1 row in Set (0.00 sec)

@ @validate_password_mixed_case_count;

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

@ @validate_password_mixed_case_count |

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

1 |

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

1 row in Set (0.00 sec)

Mysql> set global validate_password_mixed_case_count=2;

0 rows affected (0.00 sec)

@ @validate_password_mixed_case_count;

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

@ @validate_password_mixed_case_count |

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

2 |

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

1 row in Set (0.00 sec)

@ @validate_password_length;

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

@ @validate_password_length |

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

6 |

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

1 row in Set (0.00 sec)

Of course, if the Validate_password plugin must already be installed, MySQL5.7 is installed by default.

So how do you verify that the Validate_password plugin is installed? You can view the following parameters, and if not installed, the output will be empty.

mysql> SHOW VARIABLES like ' validate_password% ';

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

| variable_name | Value |

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

|       Validate_password_dictionary_file | |

6 |

2 |

1 |

| Validate_password_policy | Low |

1 |

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

6 rows in Set (0.00 sec)

Seven: schedule: Character setting

[Email protected] ~]# CAT/ETC/MY.CNF

[Mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

User=mysql

# Disabling Symbolic-links is recommended to prevent assorted

Security risks This is a complete sentence, not fit, divided into two lines

Symbolic-links=0

#default-character-set = UTF8 in this version, do not add this sentence, otherwise it will cause the problem of character set conflict, mysqld can not start

Character_set_server=utf8

init_connect= ' SET NAMES UTF8 '

[MySQL]

Default-character-set = UTF8

[Mysqld_safe]

Log-error=/var/log/mysqld.log

Pid-file=/var/run/mysqld/mysqld.pid

Default-character-set = UTF8

[Email protected] ~]#

Viii. 5 ways to safely and quickly modify MySQL database names

1. RENAME DATABASE db_name to New_db_name
This one.. This syntax was added to the mysql5.1.7, and the 5.1.23 was removed.
It is said that data may be lost. It's better not to use it.
See: http://dev.mysql.com/doc/refman/5.1/en/rename-database.html

2. If all tables are MyISAM type, you can change the name of the folder
Close Mysqld
Rename the Db_name directory in the data directory to New_db_name
Open mysqld

3. rename All tables

The code is as follows:

CREATE DATABASE New_db_name;
RENAME TABLE Db_name.table1 to New_db_name.table1,
Db_name.table2 to New_db_name.table2;
DROP DATABASE db_name;

4. mysqldump Export data and import

The code is as follows:

Mysqldump-uxxxx-pxxxx-h xxxxdb_name > Db_name_dump. Sql
Mysql-uxxxx-pxxxx-h xxxx-e "CREATE DATABASE new_db_name"
mysql-uxxxx-pxxxx-h xxxx New_db_name < Db_name_dump. Sql
Mysql-uxxxx-pxxxx-h xxxx-e "DROP DATABASE db_name"

5. Use shell scripts to rename all tables

The code is as follows:

#!/bin/bash

mysqlconn= "Mysql-u xxxx-pxxxx-s/var/lib/mysql/mysql.sock-h localhost"
olddb= "Db_name"
newdb= "New_db_name"

# $mysqlconn-E "CreateDatabase $newdb"
params=$ ($mysqlconn-N-E "SELECT table_name Frominformation_schema. TABLES WHERE table_schema= ' $olddb ' ")

Forname in $params; Do
$mysqlconn-E "renametable $olddb. $name to $newdb. $name";
Done

# $mysqlconn-E "Dropdatabase $olddb"

is the optimized version of Method 3.
Small note: These operations are dangerous, so please back up your database before performing the operation!!

Nine, uninstall MySQL database

Check to see if MySQL was installed before, remove the previously installed MySQL

# Rpm-qa | Grepmysql

# rpm-emysql-libs-5.1.71-1.el6.x86_64--nodeps

# Find/-namemysql

# RM-RF ...

Note: Force uninstall is not uninstalled,--nodeps--force

Mysql5.7.20 Installation Documentation