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

1. Basic view and login:

Mysql-uroot-p

Show databases; # # View the database

Use mysql; # # enter the database mysql

Show tables; # # View the table

Desc user; # # View the structure and header of the table

two。 Table operation: check, change, delete, add

Select * from user\ G; # # query all data records in the user table

Select host,user,password from user; # # specify the fields of the user table for query

Update mysql.user set password=password ("123123") where user= "root"; # # modify root password

Delete from mysql.user where user= ""; # # Delete data records where the user is empty

Create database auth; # # create a library auth

Create table auth.users (user_name char (16) not null, user_passwd char (48) default'', primary key (user_name)); # # create table auth.users

Insert into auth.users values ('hehe','pwd@123'); # # New record

Drop table auth.users; # # Delete table users

Drop database auth; # # Delete library auth

Authority Management of 3.mysql

Grant all on library. * to user @ client address identified by 'password'

Show grants for user @ client address

Revoke permission list on library. * from user @ client address

Grant select,delete on mysql.user to 'useradm'@'192.168.100.100' identified by' 123123'

Show grants for 'useradm'@'192.168.100.100'

Revoke select,delete on mysql.user from 'useradm'@'192.168.100.100'

Delete from mysql.user where user='useradm'

Flush privileges

4. Backup and restore mysql

Log in to mysql

Create database auth

Quit

/ etc/init.d/mysqld stop

Cd / usr/local/mysql/data

Cp-rf mysql/user.* auth/

Chown mysql:mysql auth/-R

Chmod 755 auth

Chmod 660 auth/*

/ etc/init.d/mysqld start

Log in to mysql

Use auth

Show tables; # # can see the user table, desc can view the structure, select

Cold backup of mysql:

/ etc/init.d/mysqld stop

Tar Jcf / opt/mysql-bak-$ (date +% F) .tar.xz / usr/local/mysql/data

Simulated failure:

/ etc/init.d/mysqld start

Mysql login

Drop database auth

Quit

/ etc/init.d/mysqld stop

Mysql recovery:

Tar Jxf / opt/mysql-bak-*.tar.xz-C / root

Cd / root/usr/local/mysql/data

Cp-rf auth/ / usr/local/mysql/data

Chown mysql:mysql / usr/local/mysql/data/auth-R

Cd / usr/local/mysql/data

Chmod 755 auth

Chmod 660 auth/*

/ etc/init.d/mysqld start

Mysql login authentication

Show databases; # # data has been restored

Online backup; mysqldump

Netstat-utpln | grep 3306 # # make sure mysql starts

Mysqldump-uroot-p123123-- all-databases > / opt/all.sql # backup

Mysqldump-uroot-p123123-- all-databases-- lock-talbes=0 > / opt/all.sql

Mysql-uroot-p123123 create user linuxfan identified by '123123 encrypted; # # identified by stores plain text password encryption as / hash value

You can use mysql > rename user linuxfan to fage;##mysql 5 later. You need to update the user table using update before.

Mysql > set password for fage=password ('123'); # #: to be used in the mysql.user table

Mysql > update mysql.user set password=password ('123') where user='fage';##

Specify the root location of the data in the table without entering the table.

Mysql > show grants for fage; View user permissions

Mysql > grant select on mysql.user to fage; # # Grant permissions

Mysql > revoke select on mysql.user from fage; # # an error will occur if the permission does not exist

When deleting a user before mysql > drop user fage; # # 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.

7. Set the mysql5.5 display Chinese name:

Vi / etc/my.cnf

[client]

Default-character-set = utf8

[mysqld]

Character-set-server = utf8

Init_connect='SET NAMES utf8'

: wq

/ etc/init.d/mysqld restart

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.