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

The following content mainly brings you MySQL database password, authorization, backup and recovery, the knowledge here is slightly different from books, are summed up by professional and technical personnel in the process of contact with users, have a certain experience sharing value, hope to bring help to the majority of readers.

Setting of root user password for database administrator

(a) know the old password change mysqladmin-hlocalhost-uroot-p password "abc123" enter oldpasswd 123456okmysql-uroot-pabc123456 (b) do not know the old password / etc/ my.cnf [mysqld] skip-grant-tables / login systemctl restart mysqldmysql > update mysql.user set authentication_string=password ("new password") where user= "root" and host= "localhost" > flush privaileges / do not restart MySQL service to make the setting take effect > quit/etc/ my.cnf [mysqld] # skip-grant-tables / login without verifying password systemctl restart mysqldmysql-hlocalhost-uroot-pabc123 > # # MySQL user authorization and revocation

(a) user authorization grant permission list on library. Table to user name @ "client address" identified by "password" [with grant option] permission means: all (all permissions) usage (no permission, only login) select (query) delete (delete) insert (add) update (modify). Library name: library name. Table name *. * (all libraries. All tables) Library name. * (all tables under the library) user name: custom client address:% all addresses 192.168.4.12 (only this address is allowed) 192.168.4% (network segment 4.0 is allowed). Password: login password of authorized user with grant option / can have authorized permissions [CVM 192.168.4.11] # grant all on *. * to user@ "192.168.4.12" identified by "123456" with grant option / user user logs in to the database on the 192.168.4.12 host, with permissions for all libraries, all tables and authorizable permissions > show grants for user@ "192.168.4.12" / permissions to view authorized users on the server > set password for user@192.168.4.12=password ("New password") / change the password for the authorized user on the server [client 192.168.4.254] # mysql-h292.168.4.11-uuser-p123456mysql > > show grants; / client view permission > set password=password ("new password"); / authorize the user to change the password himself (b) revoke the permission (1) revoke the revoke permission list on library name by command. Table name from user name @ "client address" # revoke all on *. * from user@192.168.4.12; (2) revoke use mysql > show tables by modifying the database table record Use already has authorized user information db authorized user access to the library tables_priv authorized user access to the table columns_priv authorized user access to fields in the table # update user set delete_priv= "N", drop_priv= "N" where user= "user" and host= "192.168.4.254"; / revoke deletion, change permission flush privileges;show grants for user@192.168.4.254 # # data backup and recovery (full backup and full recovery) backup method (a) physical backup: backup of files corresponding to direct copy libraries or tables # cp-rp / var/lib/mysql/gamedb / allbak recovery # cp-rp / allbak / var/lib/mysql/gamedb requires attention to the permissions of the library when using physical backup. All groups must be mysql (b) logical backups: when performing a backup, generate corresponding sql commands according to existing libraries and tables, and store the generated sql commands in the specified backup file. # mysqldump-u user name-p password name > / directory / name. SQL library name representation:-- all-databases / backup database server all database names / backup all database tables of a library / backup all data of a table-B library name 1 library name 2. / backup all data from multiple libraries together # mysqldump-uroot-p "abc123" gamedb > / allbak/mysql.sql restore # mysql > create database gamedb / you need to create a library # mysql-uroot-pabc123 gamedb when restoring

/ allbak/mysql- `date +% F`.SQL systemctl start crond crontab-e

For the above MySQL database password, authorization, backup and recovery, if you need to know more, you can continue to pay attention to the innovation of our industry, if you need professional answers, you can contact the pre-sale and after-sales on the official website. I hope this article can bring you some knowledge updates.

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.