Recover database administrator password, user authorization, data backup and recovery

Modify the database administrator password
# mysqladmin-hlocalhost-uroot-p Password "new

Code
Enter Password: Current login password

Restore the database administrator password
#vim/etc/my.cnf
[Mysqld]
#validate_password_policy =0
#validate_password_length =6
Skip-grant-tables
: Wq
#systemctl Restart Mysqld

#mysql
Mysql>
Update Mysql.user Set

Authentication_string=password ("123QQQ") where

host= "localhost" and user= "root";
mysql> flush Privileges;
Mysql> quit
#vim/etc/my.cnf
[Mysqld]
Validate_password_policy=0
Validate_password_length=6
#skip-grant-tables
: Wq
#systemctl Restart Mysqld
#mysql-UROOT-P123QQQ
Mysql>

User authorization
What is authorization: Add a new user who can connect on the database
The default database administrator root user has authorization permissions when they log on natively.

What is the format of the authorization command?
Grant permission list on data name to user name;

Grant permission list on data name to User name @ "Client Address";

Grant permission list on data name to User name @ "Client Address"

Identified by "password";

Grant permission list on data name to User name @ "Client Address"

Identified by "Password" with GRANT option;

How is the permission list represented?
All permissions
Select,update (Name,age)

How is the data name represented?
* * All libraries All tables
Library name. * One Library
Library name. Table Name one table

User name: Authorization when custom to have identity, is the user login is used by the name

How is the client address represented?
% all addresses
192.168.2.% Network Segment
%.tarena.com Area
Pc101.tarena.com Host Name

Identified password used when connecting by "password"

With GRANT option has authorization permissions

Who to authorize?
Authorization to users (Web server)
Manager (Maintenance engineer)

Allows the database administrator root user to connect to the database server on a 254 host,

The connection password is abc123 after the connection has full permissions and has authorized permissions
Mysql>grant all on * * to [email protected] "192.168.4.254"

Identified by ' abc123 ' with GRANT option;

Mysql>grant select,update (name) on Userdb.user to

Student identified by "123456";

Select User (); Displays the user name and client address of the login
Show grants; Connect users to view their access rights

Logon Test Authorization on client
#which MySQL
#yum-y Install mariadb
#mysql-h192.168.4.12-uroot-pabc123
Mysql>
Grant all on bbsdb.* to [email protected] "192.168.4.11"

Identified by "123456";
+++++++++++++++++++++++++++++++
Authorization information is stored in the authorization library MySQL Library
User's existing authorized users and access rights
DB Records authorized user access to the native library
Tables_priv record authorized users ' access to native tables
Columns_priv record authorized users ' access to fields in the native table

View existing authorized users and permissions
Select User,host from Mysql.user;
Database administrator View permissions information for authorized users
Show grants for user name @ "Client Address";

Revoke user Authorization
Revoke permissions list on library name from user name @ "Client Address";

Mysql> revoke GRANT OPTION on * * FROM

' Root ' @ ' 192.168.4.254 ';

mysql> revoke Delete on bbsdb.* from

' WebAdmin ' @ ' 192.168.4.11 ';

Mysql> Show grants for ' WebAdmin ' @ ' 192.168.4.11 ';

Mysql>update Mysql.db Set

insert_priv= "n", update_priv= "n" where db= "Bbsdb";
mysql> flush Privileges;

Mysql> Show grants for ' WebAdmin ' @ ' 192.168.4.11 ';

Mysql> revoke all on bbsdb.* from

' WebAdmin ' @ ' 192.168.4.11 ';

Remove an authorized user
Method One:

Mysql>delete from Mysql.user where

User= "WebAdmin" and host= "192.168.4.11";
mysql> flush Privileges;

Method Two
Mysql> Drop user username @ "Client address";

Authorize users to log in and modify their login password
SET password=password (' New password ');

Administrator root reset the login password for authorization
SET PASSWORD
For username @ ' Client address ' =password (' new password ');
++++++++++++++++++++++++++++++++
Installing MySQL Graphics management tool phpMyAdmin
#yum-y Install httpd PHP
#systemctl start httpd; Systemctl Enable httpd
[Email protected] ~]# cat/var/www/html/test.php
<?php
Phpinfo ();
?>
[Email protected] ~]#

Client Access
Firefox http://192.168.4.12/test.php

# TAR-ZXVF Phpmyadmin-2.11.11-all-languages.tar.gz

-c/var/www/html/

#cd/var/www/html/
#mv Phpmyadmin-2.11.11-all-languages/phpmyadmin
#chown-R Apache:apache phpMyAdmin
#cd phpMyAdmin
#cp config.sample.inc.php config.inc.php
#vim config.inc.php
$cfg [' blowfish_secret '] = ' plj123 ';
$cfg [' Servers '] [$i] [' host '] = ' localhost ';
: Wq
# yum-y Install Php-mysql
#mysql-UROOT-P123QQQ
Mysql>create database webdb;
Mysql>grant all on webdb.* to [email protected] "localhost"

Identified by "123456";

Client Access
Firefox http://192.168.4.12/phpmyadmin
User WebUser
Password 123456
+++++++++++++++++++++++++++++++++
Data backup and Recovery
1 Why should I back up my data?
Use Backup files to recover data when data is lost or mistakenly deleted.

2 How is data backed up?
Physical backup? Backup library or table corresponding file
Cp-r/var/lib/mysql/mysql/opt/mysql.bak
Cp/var/lib/mysql/mysql/user.*/opt/

tar-zcvf/opt/mysql.tar.gz/var/lib/mysql/mysql/*

164 Cp-r/mydata/mysql.bak//var/lib/mysql/mysql
165 Chown-r Mysql:mysql/var/lib/mysql/mysql
166 Systemctl Restart Mysqld

Logical backup? The corresponding SQL commands are generated from the existing library tables and records at the time of backup.

SQL saved to the specified backup file

3 Data backup strategy?
Full backup back up all data (one server, one library, one table)
Differential backup backup all new generation since full backup
Incremental backup backups are all newly generated since the last backup

Full backup + differential backup
Full backup + Incremental backup

4 How to implement data backup in a build environment
Recurring Scheduled Tasks perform backup scripts

* * 1 sh/shell/allbak.sh

Recover database administrator password, user authorization, data backup and recovery