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

This article mainly introduces Mysql database administrator password how to set the problem analysis, I hope you can add and update some knowledge, if there are other problems you need to know, you can continue to pay attention to my update article in the industry information.

I. Database administrator password setting:

1, modify the database administrator local login password (operating system administrator has the right to modify)

mysqladmin -hlocalhost -uroot -p password "new password"

2. Restore database administrator password (operating system administrator has permission to modify)

#systemctl stop mysqld

#vim /etc/my.cnf

[mysqld]

skip-grant-tables //Skip password verification

#systemctl start mysqld

#mysql

mysql> update mysql.user set authentication_string=password("new password") where user="root" and host="localhost";

mysql> flush privileges;

mysql> quit;

#vim /etc/my.cnf

[mysqld]

#skip-grant-tables //will skip validation comments

#systemctl stop mysqld

#systemctl start mysqld

#mysql -uroot -p new password//login verify password is set successfully

mysql>

+++++++++++++++++++++++++++++++++++++++

II. User authorization: Add a new connected user to the database Cloud Virtual Machine.

User authorization:

Command:

mysql> grant permission list on library name to username @"client address" identified by "password" [ with grant option];

//with grant option Authorized users have authorization rights.

Permission is expressed as:

all 、 usage 、select,update(name,age),delete

Library name representation:

Name of library. table name

Name of library.

.*

User Name:

custom

Client address representation:

192.168.4.117 a single host

192.168.2.% //indicates all hosts on segment 192.168.2.0

identified by "password" //Login password

with grant option //can have grant option

####################################################################

Permission Revocation:

Command:

mysql> revoke permissions list on library name from username @"client address";

##################################################################

Delete authorized user: drop user username @"client address";

##################################################################

View the privileges of authorized users show grants for user @"client address";

Authorization information storage location: in the authorization library mysql table. (Different authorization information stored differently)

mysql>use mysql;

mysql>show tables;

user select user,host from mysql.user;

db Authorizes user access to libraries

tables_priv records the access rights of authorized users to tables

columns_priv records the access rights granted to the fields in the table.

########################################################################

Examples:

Allow the administrator user on host 192.168.4.254 to connect to the database server.

mysql>grant all on . to root@"192.168.4.254" identified by "123456" with grant option;

Bbsuser connection password is 123456.

grant all on bbsdb.* to bbsuser@"192.168.4.30" identified by "123456";

Run using admin user on database server local login password 123456 only query table records permission.

grant select on . to admin@"localhost" identified by "123456";

mysql> revoke permissions list on library name from username @"client address";

Revoke root login authorization on host 254

revoke grant option on . from root@"192.168.4.254";

Revoke root's permission to delete records and modify records on host 254 login

revoke delete,update on . from root@"192.168.4.254";

Revoke all privileges of root login on host 254

revoke all on . from root@"192.168.4.254";

#############################################################################

Revoke user privileges by modifying table records.

1 Modify record information in correspondence table

2 fulsh privileges;

##########################################################################

After authorized users log on to the server, modify the login password;

mysql> set password=password("password");

Administrator resets login password for authorized users

mysql> set password for user @"client address"=password("password");

########################################################################

Client test authorization:

#which mysql

#rpm -q mariadb

#mysql -h IP address of database server-u username-p password

mysql> select @@hostname;

mysql> select user();

mysql> show grants;

############################################################################

Data backup and recovery (full backup and full recovery)

Backup method:

1. Physical backup: directly copy the file corresponding to the library or table (Myisam)

cp -r /var/lib/mysql/mysql /mysql.bak

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

cp -r /mysql.bak /var/lib/mysql/mysql

chown -R mysql:mysql /var/lib/mysql/mysql

########################################################

2. Logical backup: When performing backup, generate corresponding sql commands according to existing libraries and tables, and store the generated sql commands in specified backup files.

Backup policy:

Full backup: backup all data (tables, libraries, servers)

Differential backup: backup all newly generated data since full backup (backup newly generated data)

Incremental backup: Backup all new backups made since the last backup. (Backup newly generated data)

Issues to consider when backing up a database:

Backup frequency Backup time Backup policy Storage space Naming of backup files (usually in the form of.sql)

How backup policies are used:

Full + incremental (used more in work environments)

Total + Difference

How to perform data backups: Periodically scheduled tasks crond executes backup scripts

########################################################################

Complete backup data mysqldump

#mysqldump -uroot -pabc123 library name> directory name/name.sql

Library name representation:

--all-databases Back up all data from the database server

Backup all the data in a library

A database table backs up all the data of a table.

-B Library name 1 Library name 1 Library name N Backup all data of multiple libraries together

#########################################################################

Complete recovery of data

#mysql -uroot -pabc123 studb

< 目录名/名.sql //studb库应提前创建 ######################################################################### 备份脚本： mkdir /srcriptdir vim /srcriptdir/userdbbakall.sh #!/bin/bash day=date +%F if [ ! -e /bakdir ];then mkdir /bakdir fi mysqldump -uroot -pabc123 userdb >

/bakdir/userdb-$day.sql

:wq

#chmod +x /srcriptdir/userdbbakall.sh

#/srcriptdir/userdbbakall.sh

#ls /bakdir/.sql

#vim /bakdir/.sql

#crontab -e

30 23 1 /srcriptdir/userdbbakall.sh &> /dev/null

:wq

Read the above on Mysql database administrator password how to set the problem analysis, I hope to give you some help in the actual application. Due to the limited space of this article, it is inevitable that there will be deficiencies and needs to be supplemented. If you need more professional answers, you can contact our 24-hour pre-sales service on the official website to help you answer your questions at any time.

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.