Summary of common knowledge of Mysql
This article mainly gives you a brief summary of Mysql common knowledge, related professional terms you can check online or find some related books to supplement, here is not involved, go straight to the topic, hope to bring you some practical help.
password reset
Stop mysqld first
# mysqld_safe --skip-grant-tables &
# use mysql;
# update user set password=password ('yes ') where user="root"; ---Set root password to yes
# flush privileges;
# exit
# ps -ef |grep mysql //If you find that the safe mode has not been turned off, you need to kill -9 to kill it.
# service mysqld stop
# service mysqld start
# mysql -uroot -p
#Enter new password and log in
database permission operation
Create a user and set a password
#create user 'test'@'localhost' identified by '123456'
#SET PASSWORD FOR 'username'@'host' = PASSWORD ('newpassword ') //Set or configure user password
2. Open remote connection authorization of mysql database
#create user 'frog'@'192.168.10.117' identified by 'kang'//Create user and set password
#grant all privileges on *.* to frog@192.168.10.117 identified by 'kang18' with grant option;
#flush privileges; //update cache
3. Specify database permissions for users: mysql for account abc@123 for password;
#grant all privileges on wordpress_db.* to mysql@localhost identified by 'mysql@1234';
4. No spaces are allowed between username 'mysql'@'%'. % can be replaced by a specific address. % means that any host can access it.
Remote connection database
#mysql -h 123.207.158.65 -P [port] -u root -p abc@123
//mysql -h [Cloud database external network address] -P [Cloud database port number] -uroot -p[Cloud database password]
//execute local sql script, it must be with full path
#source ~/sqlSc.sql
database backup mysqldump
1. Export entire database
//mysqldump -u username-p database name> exported file name
#mysqldump -u user_name -p123456 database_name > outfile_name.sql
2. Export a table
//mysqldump -u username-p database name table name> exported file name
#mysqldump -u user_name -p database_name table_name > outfile_name.sql
3. Import a database
#mysql -u user -p password
#use uploadDatabaseName
#source path:backupFile.sql
4, MySQL full backup
#mysqldump -uroot -p --lock-all-tables --all-databases --flush-logs --master-data=2 >/root/all.
basic operation
1. Modify the table field
#alter table tableNAME change Filedname Filedname FileType [non-empty allowed];
#alter tableNAME modify Filedname FileType [non-empty allowed];
2. Add table fields
#alter table tableNAME add addField varchar(10) not Null;
3. Delete the table field
#alter table tableNAME drop Filedname;
4. Query data
#select * from TableName;
5. Insert data
#insert into TableName (Fieldname,Fieldname) values ('FiledVlaue','FiledVlaue');
6. Delete data
#delete from TableName where Fieldnam='2';
7. Modification of data
#update TableName set Fieldnam='li' where Fieldnam ='100'
8. Joint search data
#select * from TableName1 left join TableName2 on TableName1.id=TableName2.id;
9. Look-up table structure
#desc mysql.user
Mysql common knowledge summary will first give you here, for other related issues you want to know can continue to pay attention to industry information. Our section content captures some industry news and expertise to share with you every day.
