Shulou(Shulou.com)06/01 Report--

1) install MySQL database under Centos

Install MySql reference website: https://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/

Because the yum repository is created on the MySQL official website, it is very convenient to install it directly in yum mode. The specific steps are as follows:

Configure the server's yum repository: add MySQL's yum repository to the server

a. Download from http://dev.mysql.com/downloads/repo/yum/ address

b. Select the appropriate RPM package according to the server system and version

c. Use the following example command to install the RPM package to complete the extension of the server yum repository

Rpm-Uvh mysql57-community-release-el6-n.noarch.rpm

two。 Select the version of MySQL to install

If you are installing the latest version, you do not need any settings, and if you are installing the historical version, set it with the following command:

A. yum repolist all | grep mysql / / View all available versions

B. Yum-config-manager-- disable mysql57-community / / disable drop version 5.7

C. Yum-config-manager-- enable mysql56-community / / enable version 5.6

3. Install MySQL

Yum install mysql-community-server / / run this command to install the version of step 2 enable directly

4. Start the MySQL service

Service mysqld start / / centos 6

Systemctl start mysqld.service / / centos 7

Service mysqld status / / View startup status

5. Log in to the MySQL database

Mysql-h localhost-u root-p / / root users of the newly installed MySQL database do not have a password and can log in to the command line mode by pressing enter directly

Special reminder:

If you install it in Centos 7 or above, you will not be able to log in successfully.

The following processing steps are required:

After installing MySQL in centos 7 and above, every time a root user tries to log in, a random password is produced and stored in the var/log/mysqld.log file, so you can run the grep "password" / var/log/mysqld.log command to get the random password:

Then, run the mysql-h localhost-u root-p command and log in to command mode with a random password, followed by the second part of the user and rights management operation.

For more information, please refer to the following website: https://blog.csdn.net/z13615480737/article/details/78906598

II) MySQL users and rights management

User management reference website: https://www.cnblogs.com/fslnet/p/3143344.html

1. To set the password for the root user for the first time, run the following command:

Mysql > set password for 'root'@'localhost' = password (' 123456'); / / set the password to 123456

two。 User management

Mysql > use mysql

View

Mysql > select host,user,password from user

Create

Mysql > create user zx_root IDENTIFIED by 'xxxxx'; / / identified by stores plain text password encryption as a hash value

Modify

You can use mysql > rename user feng to newuser;//mysql 5 later. You need to update the user table using update before.

Delete

When deleting a user before mysql > drop user newuser; / / mysql5, you must first use revoke to delete the user rights, and then delete the user. After mysql5, the drop command can delete the user while deleting the relevant permissions of the user.

Change password

Mysql > set password for zx_root = password ('xxxxxx')

Mysql > update mysql.user set password=password ('xxxx') where user='otheruser'

3. View user permissions

Mysql > show grants for zx_root

Grant authority

Mysql > grant select/all on dmc_db.* to zx_root

Reclaim permission

Mysql > revoke select on dmc_db.* from zx_root; / / an error will be reported if the permission does not exist

The above command can also use multiple permissions to grant and reclaim simultaneously, separated by commas.

Grant select on testdb.* to common_user@'%'

Grant insert on testdb.* to common_user@'%'

Grant update on testdb.* to common_user@'%'

Grant delete on testdb.* to common_user@'%'

Or, replace it with a MySQL command:

Grant select, insert, update, delete on testdb.* to common_user@'%'

If you want to see the results immediately, use

Flush privileges

Command update

The following information must be given when setting permissions

1, permissions to be granted

2, the database or table to which access is granted

3, user name

Grant and revoke can control access at several levels

1, the entire server, using grant ALL and revoke ALL

2, the whole database, using on database.*

3, feature table, using on database.table

4, specific column

5, specific stored procedures

The meaning of the value of host column in user Table

% match all hosts

Localhost localhost will not be resolved to an IP address, but will be connected directly through UNIXsocket

127.0.0.1 will connect through the TCP/IP protocol and can only be accessed locally

:: 1:: 1 is compatible with ipv6, representing 127.0.0.1 of the same as ipv4

4. Permission to grant an instance

Grant database developer, create tables, indexes, views, stored procedures, functions. Equal authority

Grant creates, modifies, and deletes MySQL data table structure permissions.

Grant create on testdb.* to developer@'192.168.0.%'

Grant alter on testdb.* to developer@'192.168.0.%'

Grant drop on testdb.* to developer@'192.168.0.%'

Grant manipulates MySQL foreign key permissions

Grant references on testdb.* to developer@'192.168.0.%'

Permission for grant to operate MySQL temporary table

Grant create temporary tables on testdb.* to developer@'192.168.0.%'

Grant operation MySQL index permissions

Grant index on testdb.* to developer@'192.168.0.%'

Grant manipulates MySQL view, view view source code permissions

Grant create view on testdb.* to developer@'192.168.0.%'

Grant show view on testdb.* to developer@'192.168.0.%'

Grant manipulates MySQL stored procedures and function permissions

Grant create routine on testdb.* to developer@'192.168.0.%'; / / now, can show procedure status

Grant alter routine on testdb.* to developer@'192.168.0.%'; / / now, you can drop a procedure

Grant execute on testdb.* to developer@'192.168.0.%'

Permissions for grant ordinary DBA to manage a MySQL database

Grant all privileges on testdb to dba@'localhost'

The keyword "privileges" can be omitted.

Grant Advanced DBA manages permissions for all databases in MySQL

Grant all on *. * to dba@'localhost'

MySQL grant permissions, which can be used at multiple levels.

1. Grant acts on the entire MySQL server:

Grant select on *. * to dba@localhost; / / dba can query tables in all databases in MySQL.

Grant all on *. * to dba@localhost; / / dba can manage all databases in MySQL

2. Grant acts on a single database:

Grant select on testdb.* to dba@localhost; / / dba can query tables in testdb.

3. Grant acts on a single data table:

Grant select, insert, update, delete on testdb.orders to dba@localhost

4. Grant acts on the columns in the table:

Grant select (id, se, rank) on testdb.apache_log to dba@localhost

5. Grant acts on stored procedures and functions:

Grant execute on procedure testdb.pr_add to 'dba'@'localhost'

Grant execute on function testdb.fn_add to 'dba'@'localhost'

Note:

a. After modifying permissions, be sure to refresh the service, or restart the service, refresh the service with FLUSH PRIVILEGES.

B. By default, there is an account with an empty user name in MySQL. As long as you are local, you can log in to MySQL without entering the account password. Because of the existence of this account, the new users cannot log in with the account password, just log in as the root user and delete it.

Mysql-u root # Log in to MySQLuse mysql as root account # Select mysql Library delete from user where User=''; # Delete the line with empty account flush privileges; # Refresh permission exit # exit mysql

c. Run the following command to enable root users to log in remotely

Mysql > grant all privileges on *. * to 'root'@'%' identified by' 123456 'with grant option

Permission table

Permission description all

Alter

Alter routine uses alter procedure and drop procedurecreate

Create routine uses create procedurecreate temporary tables, uses create temporary tablecreate user

Create view

Delete

Drop

Execute uses call and stored procedures file can use select into outfile and load data infilegrant option can use grant and revokeindex can use create index and drop indexinsert

Lock tables Lock Table process uses show full processlistreload to use flushreplication client server locations to access replocation slave by replication slaves using select

Show databases

Show view

Shutdown uses mysqladmin shutdown to turn off mysqlsuper

Update

Usage has no access permission

III) create a remote login

1. First of all, specific users are authorized to have remote login rights. For specific methods, please refer to the second part above.

two。 Secondly, the MySQL database uses port 3306 by default, and port 3306 needs to be opened to the outside world before users can log in to the database system from the remote side.

A. centos 7 executes the following command to open port 3306 to the public:

# / sbin/iptables-I INPUT-p tcp-- dport 3306-j ACCEPT

# / etc/rc.d/init.d/iptables save

# / etc/init.d/iptables status

B. Centos 7 and above execute the following command to open port 3306 to the public:

# firewall-cmd-zone=public-add-port=3306/tcp-permanent

# firewall-cmd-reload

IV) Database backup and recovery

Backup

Backup database command, there is no difference between the two commands

Mysqldump-hhostname-uusername-p databasename > backupfile.sql

Or

Mysqldump-hhostname-uusername-p-- add-drop-table databasename > backupfile.sql

Note: there is no database creation statement for the data of these two commands dump, so if the target database does not exist during recovery, you need to create it manually first!

two。 Backup multiple MySQL databases at the same time

Mysqldump-hhostname-uusername-p-- databases databasename1 databasename2 databasenameN > backupfile.sql

3. Just back up the table structure

Mysqldump-hhostname-uusername-p-no-data-- databases database1 database2 databaseN > backupfile.sql

Note: the data of these two commands dump contains database creation statements!

4. Back up only some tables in the database

Mysqldump-hhostname-uusername-p databasename specify_table1 specify_table2 > backupfile.sql

5. Back up all databases

Mysqldump-hhostname-uusername-p-- all-databases > backupfile.sql

6. Compress backup of database

Mysqldump-hhostname-uusername-p databasename | gzip > backupfile.sql.gz

Restore

1. Restore database command

Mysql-hhostname-uusername-p databasename < backupfile.sql

two。 Restore from multiple or all database backups

Mysql-hhostname-uusername-p-one-database databasename < backupfile.sql / / restore a specific database, the target library must exist

Mysql-hhostname-uusername-p < backupfile.sql / / restore all databases from multiple backups at once. The target database does not exist and can be created automatically.

3. Restore a compressed MySQL database

Gunzip < backupfile.sql.gz | mysql-hhostname-uusername-p databasename

Note:

The logic of recovery is:

1. Tables created after the backup will be retained

two。 The table in the backup is deleted, or the data in the backup is modified.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.