04: User authorization and revocation, data backup and recovery, MySQL management tools

Day04
First, manage the root user password
1.1 Change Password
1.2 Recover Password

Second, user authorization and REVOKE authority
2.1 Licensing
2.2 Revoke permissions

III. installation of graphical management tools

Four-data backup and recovery-----full backup and recovery
4.1 Full backup
4.2 Full Recovery
++++++++++++++++++++++++++++++++++
One, the management root user password (* Operating system administrator has rights Management)
1.1 Modifying the native login password
#mysqladmin-hlocalhost-uroot-p Password "New password"
Must know the correct old password new password to set the success

1.2 Restore Native Login password
#vim/etc/my.cnf
[Mysqld]
Skip-grant-tables
#validate_password_policy =0
#validate_password_length =6
...
: Wq
#systemctl Stop Mysqld
#systemctl Start mysqld

#mysql
mysql> desc Mysql.user;
Authentication_string

Mysql> select host,user,authentication_string from Mysql.user;
mysql> Update Mysql.user Set Authentication_string=password ("123456")
where host= "localhost" and user= "root";

mysql> flush Privileges;
Mysql> quit;
#vim/etc/my.cnf
[Mysqld]
#skip-grant-tables
Validate_password_policy=0
Validate_password_length=6
...
: Wq
#systemctl Stop Mysqld
#systemctl Start mysqld
#mysql-uroot-p123456
Mysql>

+++++++++++++++++++++++++++++++++++++
Second, user authorization and REVOKE authority
2.1 Authorization (Add a new connection user on the database server)
Authorization Command Syntax format
Mysql> grant permission list on library name to User name @ "client Address" identified by "password" with GRANT option;

Permissions List:
All
Select, INSERT, update (field name)
Usage

Library Name:
.
Library name. *
Library name. Table Name

User name: When you connect to the database server, you can customize the name when you authorize it.

Client Address:
%
192.168.4.12
192.168.4.%

pc100.tedu.cn
%.tedu.cn
localhost

Identified by "Password" login password
With GRANT option has authorization permissions (optional)
Example 1
Grant all on . to [email protected] '% ' identified by ' 654321 ' with GRANT option;

Test authorization on the client:
Ping-c 2 192.168.4.51
1 Check if there is a command line connection command #which MySQL
#yum-y Install mariadb
2 Connecting to the database server
#mysql-H Database server IP address-u user name-p password

#mysql-h192.168.4.51-uroot-p654321
Mysq> SELECT @ @hostname;
Mysql> Select User ();
Mysql> Show grants;
Mysql> Grant all on bbsdb.* to [email protected] "192.168.4.53"
-Identified by "654321";

Server-side display information of the currently running Program Access users?
Mysql> show Processlist;

In 53 host test authorization?
#mysql-h192.168.4.51-ubbsuser-p654321
Mysql> Show grants;
++++++++++++++++++++++++++++++++++
The MySQL authorization library stores the authorization information,
Use different tables to store different authorization permissions.
User stores authorized users already owned by authorized users
DB Storage authorized user access to the library
TABLES_PRIV Store authorized user access to a table
COLUMNS_PRIV Store authorized user access to fields in a table

Show grants for user name @ "Client Address";
++++++++++++++++++++++++++++++++++
2.2 Revoke permissions (performed on the database server)
A view existing authorized users
Select User,host from Mysql.user;
B View existing authorized user access rights
Show grants for user name @ "Client Address";
C Revoke User access rights
Revoke permissions on the library name from the user name @ "Client Address";

Revoke delete,update on BBSDB. from [email protected] "192.168.4.53";
Select from mysql.db where user= "Bbsuser" \g;

Update mysql.db set delete_priv= "Y" where user= "Bbsuser" and host= "192.168.4.53";
Flush privileges;

Revoke grant OPTION on . from [email protected] '% ';
Revoke all on . from ' root ' @ '% ';
Show grants for [email protected] '% ';

Delete an authorized user you've added?
Drop user username @ "Client address";

Authorize users to log on to the server and modify their login password?
SET Password=password ("New password");

The administrator modifies the login password of the authorized user?
SET PASSWORD
For user name @ "Client Address" =password ("New password");
+++++++++++++++++++++++++++++++++
See what authorized users are available on the server?
Show access rights for an authorized user?

Modify the login password bit 777777 for an authorized user and test on the client
After an authorization login, modify your login password to 666666 and test on the client

Revoke permission for an authorized user to delete records and update records for all tables in the library. and tested on the client.

Revoke all access rights for a user. And in the client test

Remove all authorized users to allow only the database administrator to log on natively. and tested on the client.

Authorized Tom users can have full permissions on the student library with a native login password of 654321. and have authorization rights, and test authorization.

+++++++++++++++++++++++++++++++++++++++
Third, install the Graphics management tool phpMyAdmin (install the database server)
1 rpm-q httpd php php-mysql
2 yum-y install httpd PHP php-mysql
3 Systemctl start httpd; Systemctl Enable httpd
4 TAR-ZXF phpmyadmin-2.11.11-all-languages.tar.gz-c/var/www/html/

5 cd/var/www/html/

6 MV Phpmyadmin-2.11.11-all-languages PhpMyAdmin

7 Chown-r Apache:apache phpmyadmin/

8 CD phpmyadmin/

9 CP config.sample.inc.php config.inc.php

Ten sed-n ' 17p;31p ' config.inc.php
$cfg [' blowfish_secret '] = ' plj123 ';
$cfg [' Servers '] [$i] [' host '] = ' localhost ';

mysql-uroot-p123456
Mysql> Grant all on gamedb.* to [e-mail protected] "localhost" identified by "123456";

254 Client:
#firefox Http://192.168.4.51/phpmyadmin

+++++++++++++++++++++++++++++++++++++++++++
Four-data backup and recovery-----full backup and recovery
4.1 What is the purpose of data backup? Recover data using a backup.
4.2 How is data backed up? Physical and logical backups
Physical Backup: Directly copies the files that correspond to the library or table.
Cp-r/var/lib/mysql/mysql/opt/mysql.bak
tar-zcvf/opt/mysql.tar.gz/var/lib/mysql/mysql/*

              cp  /opt/mysql.bak  /var/lib/mysql/mysql              chown -R  mysql:mysql  /var/lib/mysql/mysql              systemctl restart mysqld              有局限性: myisam  跨平台性查                                    数据量备份恢复浪费时间

Logical backup: When performing a backup, the corresponding SQL commands are generated according to the existing data, and SQL is saved to the specified file. When the SQL command in the backup file is executed, the data is written back into the database.

4.3 Data backup strategy? Full backup differential backup incremental backup
Full backup: Back up all data
Back up all data on a single database server
Back up all data for a single database
Back up all of the data for a single table database

Differential backup: Backs up all newly generated data since the full backup.
Incremental backup: Backs up all newly generated data since the last backup.

Means of performing data backup operations:
To execute a backup script using the Crond task (Shell/python)

Things to consider when backing up data:
Cycle time policy file name storage space

What are the backup strategies used in your work?
Full backup + differential backup
Full backup + Incremental backup

Cycle time Policy T1 file name data
6:00
1 Full 1.sql 10
2 Difference 3 2.sql 3
3 6 3.sql 9
4 5 4.sql 14
5 1 5.sql 15
6 2 6.sql 17
7 Difference 3 7.sql 20

Cycle time Policy T1 file name data
6:00
1 Full 1.sql 10
2 increments 3 2.sql 3
3 6 3.sql 6
4 5 4.sql 5
5 1 5.sql 1
6 2 6.sql 2
7 increments 3 7.sql 3
++++++++++++++++++++++++++++++++++++++
4.1 Full backup

[Email protected] ~]#
mysqldump-hlocalhost-uroot-p123456 Library Name > directory name/file name. sql

How the library name is represented:
Backs up all data on a single database server--all-databases
Backs up all database names for a database gamedb
Backs up all database name table names for a table database Studentdb user
Back up all data from multiple libraries to a single backup file-B library name 1 Library name 2 library name N

4.2 Full Recovery
[Email protected] ~]#
mysql-hlocalhost-uroot-p123456 Library Name < directory name/file name. sql
+++++++++++++++++++++++++++++++++++++++++++++
#mkdir/BAKDB
#vim/bakdb/allstudb.sh
#!/bin/bash
day=$ (Date +%f)
if [!-e/bakdir];then
Mkdir/bakdir
Fi
mysqldump-uroot-p123456 db4 >/bakdir/$day-db4.sql
: Wq
#chmod +x/bakdb/allstudb.sh
#/bakdb/allstudb.sh
#ls/bakdir
#crontab-E
1/bakdb/allstudb.sh &>/dev/null
: Wq
++++++++++++++++++++++++++++++++++++
What are the drawbacks of full backups?

04: User authorization and revocation, data backup and recovery, MySQL management tools