MySQL study NOTE _ MySQL

How to configure the MySQL learning notes bitsCN. comMYSQL database:
Generally, you just need to modify my. ini and point it to the Database Directory.
Liu Yongfa (yongfa365) Blog has tested the simplest method:
1. net stop mysql
2. open C: Program FilesMySQLMySQL Server 5.1my.ini
Edit
Datadir = "C:/Documents and Settings/All Users/Application Data/MySQL Server 5.1/Data /"
Is
Datadir = "D:/Data /"
3. move "C:/Documents and Settings/All Users/Application Data/MySQL Server 5.1/Data/" to another place, for example, D:/Data/
4. net start mysql
After this operation, you only need to modify my. ini to reinstall the system, and the database and corresponding permissions are all restored.

========================================================== =====
MYSQL database simple reminder:
MYSQL ends with a ';', indicating that the input ends before execution. carriage return or go is often useless.
MYSQL management tool yongfa365 Blog feels better: phpMyAdmin Navicat

========================================================== =====
How to create a user in the MYSQL database and assign permissions:
Bin> mysql-u root-p
Enter password :*********
Mysql> grant permission 1, permission 2 ,... Permission n on database name. table name to user name @ user address identified by connection password;
Permission 1, permission 2 ,... Permission n indicates 14 permissions, including select, insert, update, delete, create, drop, index, alter, grant, references, reload, shutdown, process, and file.
When permission 1, permission 2 ,... Permission n is replaced by all privileges or all, indicating that all permissions are granted to the user.
When the database name. table name is replaced by *. *, it grants the user the permission to operate all the tables in the database on the server.
The user address can be localhost, IP address, machine name, or domain name. You can also use % to connect from any address.
The connection password cannot be blank; otherwise, creation fails.

For example:
Mysql> grant select, insert, update, delete, create, drop on dbname. employee to joe@10.163.225.87 identified by 123;
Assign the user joe from 10.163.225.87 the permission to perform select, insert, update, delete, create, drop and other operations on the database dbname's employee table, and set the password to 123.
Mysql> grant all privileges on dbname. * to joe@10.163.225.87 identified by 123;
Assign the user joe from 10.163.225.87 the permission to perform all operations on all the database dbname tables and set the password to 123.
Mysql> grant all privileges on *. * to joe@10.163.225.87 identified by 123;
Assign the user joe from 10.163.225.87 the permission to perform all operations on all tables in all databases and set the password to 123.
Mysql> grant all privileges on *. * to joe @ localhost identified by 123;
Grant the local user joe the permission to perform all operations on all tables in all databases and set the password to 123.
========================================================== =====
How to change the password in the MYSQL database

First, you must declare that, in most cases, modifying MySQL requires the root permission in mysql. Therefore, you cannot change the password unless you request the administrator.
Method 1
Use phpmyadmin, which is the simplest. modify the user table of the mysql database, but do not forget to use the PASSWORD function.
Method 2
Use mysqladmin.
Mysqladmin-u root-p password mypasswd
After entering this command, you need to enter the original root password, and then the root password will be changed to mypasswd.
Change the root in the command to your username, and you can change your password.
Of course, if your mysqladmin cannot connect to mysql server, or you cannot execute mysqladmin, this method is invalid, and mysqladmin cannot clear the password.
FAQs about password change using mysqladmin:
There are many people who have modified it like this:
C:> mysqladmin-u root-p password yongfa365
Enter password :*********
Warning: single quotes were not trimmed from the password by your command line client, as you might have expected.
At this time, the real new password is yongfa365, while new users often think that the new password is yongfa365, for example:
C:> mysql-u root-pyongfa365
ERROR 1045 (28000): Access denied for user root @ localhost (using password: YES)
So it was very depressing. BAIDU and GOOGLE searched a lot.
Change the password.
C:> mysqladmin-u root-pyongfa365 password 123456
The following methods are used at the mysql prompt and must have the root permission of mysql:
Method 3
Mysql> Insert INTO mysql. user (Host, User, Password) VALUES (%, jeffrey, PASSWORD (biscuit ));
Mysql> FLUSH PRIVILEGES
Specifically, this is adding a user with the username jeffrey and password biscuit.
I wrote this example in mysql Chinese Reference Manual.
Be sure to use the PASSWORD function, and then use flush privileges.
Method 4
Similar to method Sany, but the REPLACE statement is used.
Mysql> replace into mysql. user (Host, User, Password) VALUES (%, jeffrey, PASSWORD (biscuit ));
Mysql> FLUSH PRIVILEGES
Method 5
Use the set password statement,
Mysql> set password for jeffrey @ % = PASSWORD (biscuit );
You must use the PASSWORD () function, but do not need to use flush privileges.

Method 6
Use the GRANT... identified by statement
Mysql> grant usage on *. * TO jeffrey @ % identified by biscuit;
Here, the PASSWORD () function is unnecessary and does not need to be flush privileges.

========================================================== =====
How to back up and restore a MYSQL database
The mysql directory under the backup data directory and the directory with the same name as your database
After re-installing mysql, as long as the main version is consistent (for example, mysql 5.1 and mysql 5.2, their main version is 5)
After the installation, run the net stop mysql command to stop the mysql service (you cannot back up and copy the service in the running state)
Delete the mysql folder under the newly installed mysql data directory, and copy the one you backed up to this folder.
After the Restoration, the mysql User information and related settings are the same as those of the original one.

In fact, there is a simpler method. I generally do this:
Modify the configuration of my. ini and place the mysql data storage folder to the specified partition. Even if you need to reinstall the system, after installing mysql of the same version again, modify my. ini again to direct the data storage folder back to your Directory. Methods such as how to configure the Liuyong method at the top
========================================================== =====
Common MYSQL database commands
1. connect to MYSQL.
Format: mysql-h host address-u user name-p user password
1. Example 1: Connect to MYSQL on the local machine.
First, open the DOS window, enter the directory mysqlin, type the command mysql-u root-p, and press enter to prompt you to enter the password. if you have just installed MYSQL, super User root has no password, so press enter to enter MYSQL. the MYSQL prompt is: mysql>
2. Example 2: Connect to MYSQL on the remote host. Assume that the IP address of the remote host is 110.110.110.110, the user name is root, and the password is yongfa365. Enter the following command:
Mysql-h 110.110.110.110-u root-p yongfa365
(Note: You do not need to add spaces for u and root. The same applies to others)
3. exit MYSQL command: exit (press enter)
2. change the password.
Format: mysqladmin-u username-p old password new password
1. Example 1: add a password yongfa365 to the root user. First, enter the directory mysqlbin in DOS, and then type the following command
Mysqladmin-u root-password yongfa365
Note: Because the root account does not have a password at the beginning, the old-p password can be omitted.
2. Example 2: change the root password to djg345.
Mysqladmin-u root-p yongfa365 password djg345
3. add new users. (Note: Unlike the above, the following commands in the MYSQL environment are followed by a semicolon as the command Terminator)
Format: grant select on database. * to username @ login host identified by password;
Example 1: add a user named "test1" with the password "abc" so that the user can log on to any host and have the permission to query, insert, modify, and delete all databases. First, use the root user to connect to MYSQL, and then type the following command:
Grant select, insert, update, delete on *. * to test1 @ % Identified by abc;
However, the user added in Example 1 is very dangerous. if someone knows the password of test1, then he can log on to your mysql database on any computer on the internet and do whatever he wants for your data. for the solution, see example 2.
Example 2: Add a user named "test2" with the password "abc" so that the user can only log on to localhost, you can also query, insert, modify, and delete the database mydb (localhost refers to the local host, that is, the host where the MYSQL database is located), so that the user knows the password of test2, he cannot access the database directly from the internet, but can only access the database through the web pages on the MYSQL host.
Grant select, insert, update, delete on mydb. * to test2 @ localhost identified by abc;
If you do not want test2 to have a password, you can run another command to remove the password.
Grant select, insert, update, delete on mydb. * to test2 @ localhost identified;
In the previous article, we talked about logon, adding users, and changing passwords. Next, let's take a look at the database operations in MYSQL. Note: you must first log on to MYSQL. the following operations are performed at the MYSQL prompt and each command ends with a semicolon.
I. operation skills
1. if you press enter and bitsCN.com