MySQL change data file directory and My.ini location | MySQL command detailed

Requirements: Change the MySQL data data file directory and My.ini location.

Steps:

1. Find the My.ini location, and you can get the My.ini path by viewing the executable file path, which corresponds to its properties, through the MySQL startup entry for the Windows service.

"D:\MySQL\MySQL Server 5.5\bin\mysqld"--defaults-file= "D:\MySQL Data\my.ini" MySQL55

2, edit the corresponding datadir in My.ini

Before change: datadir= "D:\MySQL data\data\"

After change: datadir= "D:\MySQL data\mysql Server 5.5\data\"

3. Reorganize MySQL data file directory (if not, MySQL will not start properly)

4. Look up the registry and modify the Defaults-file path associated with the MySQL startup entry:

Key_local_machine/system/services/mysql55

When you are finished, refresh the Windows service and look at the executable path, the MySQL startup item properties, again, with the following results:

"D:\MySQL\MySQL server 5.5\bin\mysqld"--defaults-file= "D:\MySQL data\mysql server 5.5\my.ini" MySQL

Be sure to port the My.ini to the correct directory.

5. Restart MySQL, the configuration is successful.

Additional methods:

3. Delete MySQL service: mysqld--remove MySQL56

4. Install the MySQL service (Open Bin directory): Mysqld.exe--install MySQL56--defaults-file= "D:\MySQL data\mysql Server 5.5\my.ini"

5. Restart MySQL, the configuration is successful.

Reprint: http://www.cnblogs.com/advocate/p/3433243.html

What's the difference between Mysqld.exe and Mysql.exe?

Mysqld.exe is the MySQL daemon (i.e. MySQL server). To use a client program, the program must run because the client accesses the database by connecting to the server .
Mysql.exe is a command-line client tool that comes with MySQL, which is an interactive input SQL statement or a command-line tool that executes them from a file in batch mode.
To put it simply: mysqld is the command to start the MySQL database, and MySQL is the command to open and execute the SQL statement.

Mysqld.exe command Explanation

Example of installing the MySQL service under Windows via the Mysqld.exe command line:

1. Enter cmd in "Start", "Run"
2. CD into the MySQL-installed bin directory
3. Install the MySQL service: mysqld--install
You can specify the name of the service: mysqld--install MySQL5
The default is the boot Autorun service, if the boot does not start automatically, can be written as: Mysqld.exe--install-manual
With configuration file notation: mysqld--install system service name--defaults-file= the path to your MySQL configuration file
Example: Mysqld--install MySQL5--defaults-file=d:\mysql\my.ini
4. Start: net start MySQL
5. Close: net stop MySQL
6. Delete MySQL service: mysqld--remove
You can also specify the name of the service. Mysqld--remove MySQL5

Mysql.exe's Command collection

One, connect MySQL

Format: mysql-h host address-u user name-P user Password

1. Connect to MySQL on this machine.

First open the DOS window, then enter the directory Mysql\bin, and then type the command Mysql-u root-p, enter after the prompt you to lose the password. Note that the user name can have a space or no space, but before the password must have no space, or let you re-enter the password.
If you have just installed MySQL, superuser root is no password, so the direct return to enter the MySQL, MySQL prompt is: mysql>

2. Connect to MySQL on the remote host . Assume that the remote host IP is: 110.110.110.110, the user name is root, the password is 123456. Type the following command:

Mysql-h110.110.110.110-u root-p 123456; (Note: You can not add a space between the root and the other)

3. Exit MySQL command: Exit (enter) or quit (enter)

Second, change the password.

Format: Mysqladmin-u username-P Old password password new password

1, add a password to root ab12. First enter directory Mysql\bin under DOS, and then type the following command

Mysqladmin-u Root-password AB12

Note: Because Root does not have a password at the beginning, the-p old password can be omitted.

2, then change the root password to djg345.

Mysqladmin-u root-p ab12 Password djg345

Third, add new users.
(Note: Unlike the above, the following is because it is a command in a MySQL environment, so it is followed by a semicolon as a command terminator)

Format: Grant Select on database. * To User name @ login host identified by "password"

1, add a user test1 password for ABC, so that he can log on any host, and all databases have query, insert, modify, delete permissions. First connect to MySQL with the root user, and then type the following command:

Grant Select,insert,update,delete on * * to [e-mail protected] "%" identified by "ABC";

But the added user is very dangerous, you want to like someone to know test1 password, then he can be on any computer on the Internet to log into your MySQL database and your data can do whatever you like, solution see 2.

2, add a user test2 password for ABC, so that he can only login on localhost, and the database mydb can query, insert, modify, delete operations (localhost refers to the local host , The same host as the MySQL database),

This allows the user to use a password that knows test2, and he cannot access the database directly from the Internet, but only through a Web page on the MySQL host.

Grant Select,insert,update,delete on mydb.* to [e-mail protected] identified by "ABC";

If you do not want to test2 have a password, you can call another command to erase the password.

Grant Select,insert,update,delete on mydb.* to [e-mail protected] identified by "";

Down when MySQL is on the database side of the operation. Note: You must first log in to MySQL, the following actions are performed at the prompt of MySQL, and each command ends with a semicolon.

First, the Operation skill

1, if you hit the command, enter after the discovery forgot to add a semicolon, you do not have to re-play the command, as long as a semicolon to enter the return on it.

In other words, you can break a complete command into a few lines, and then use a semicolon to make the end sign OK.

2. You can use the cursor up and down keys to recall the previous command.

Second, show the command

1. Displays the list of databases in the current database server:

Mysql> SHOW DATABASES;

Note: MySQL library has the MySQL system information, we change the password and new users, is actually using this library to operate.

2. Display the data table in the database:

mysql> use library name;
Mysql> SHOW TABLES;

3, display the structure of the data table:

mysql> DESCRIBE table name;

4. Establish the database:

mysql> CREATE database name;

5. Set up the data sheet:

mysql> use library name;
mysql> CREATE table name (field name VARCHAR (20), Field name CHAR (1));

6. Delete the database:

mysql> DROP database name;

7. Delete Data sheet:

mysql> DROP table name;

8. Empty the records in the table:

Mysql> DELETE from table name;

9. Display the records in the table:

Mysql> SELECT * from table name;

10. Insert a record into the table:

mysql> INSERT into table name VALUES ("HyQ", "M");

11. Update the data in the table:

mysql-> UPDATE table name SET field name 1= ' A ', field name 2= ' B ' WHERE field name 3= ' C ';

12. Load data into the data table in text mode:

mysql> LOAD DATA LOCAL INFILE "d:/mysql.txt" into table name;

13. Import the. sql File command:

mysql> use database name;
Mysql> SOURCE D:/mysql. SQL;

14, the command line to modify the root password:

mysql> UPDATE mysql.user SET password=password (' New password ') WHERE user= ' root ';
mysql> FLUSH privileges;

15. Display the database name of use:

Mysql> SELECT DATABASE ();

16. Display the current User:

mysql> SELECT USER ();

A city build and build tables and instances of inserting data

Drop database if exists school; Delete if school is present

Create Database School; Building a library School

Use school; Open Library School

CREATE TABLE teacher//Create tables Teacher
(
ID int (3) auto_increment NOT null primary key,
Name Char (TEN) is not NULL,
Address varchar (+) Default ' Shenzhen ',
Year Date
); End of Build table

The following is the Insert field
Insert into teacher values (", ' Allen ', ' Dalian One ', ' 1976-10-10′ ');
Insert into teacher values (", ' Jack ', ' Dalian II ', ' 1975-12-23′ ');

It is also possible to type the above commands at the MySQL prompt, but it is not easy to debug.

(1) You can write the above command as-is to a text file, assume School.sql, then copy to c:\\, and enter directory \\mysql\\bin in DOS, and then type the following command:

Mysql-uroot-p Password < C:\\school.sql

If successful, empty a row without any display, and if there is an error, there is a hint. (The above command has been debugged, you can use it only if you remove//comment).

(2) or use mysql> source c:\\school after entering the command line. SQL; You can also import the School.sql file into the database .

Iv. transferring text data to the database

1, the text data should conform to the format: The field data is separated by the TAB key, the null value is replaced by \\n. Example:

3 Rose Dalian II 1976-10-10

4 Mike Dalian One 1975-12-23

Suppose you save these two sets of data as school.txt files, placed in the C packing directory.

2, the data incoming command, load data local infile "C:\\school.txt" into the table name;

Note: You might want to copy the file to the \\mysql\\bin directory, and use the using command to hit the library that contains the table.

Five, backup database : (command in DOS \ \mysql\\bin directory execution)

1. Export the entire database

The export file is present in the Mysql\bin directory by default

Mysqldump-u user name-p database name > exported file name

Mysqldump-u user_name-p123456 database_name > Outfile_name.sql

2. Export a table

MySQLdump-u user name-P database Name Table name > exported file name

MYSQLDUMP-U USER_NAME-P database_name table_name > OUTFILE_NAME.SQL

3. Export a database structure

Mysqldump-u user_name-p-d–add-drop-table database_name > Outfile_name.sql

-D No data –add-drop-table add a drop table before each CREATE statement

4. Export with language parameters

mysqldump-uroot-p–default-character-set=latin1–set-charset=gbk–skip-opt database_name > Outfile_name. SQL

Reprint: http://www.admin10000.com/Document/63.html

MySQL change data file directory and My.ini location | MySQL command detailed