Collect MySQL usage

1. commands for starting MySql in Linux:
Mysqladmin start
/ECT/init. d/MySQL start (the installation path of MySQL is earlier)

2. Command for restarting MySql in Linux:
Mysqladmin restart
/ECT/init. d/MySQL restart (the installation path of MySQL is earlier)

3. Run the following command to disable MySql in Linux:
Mysqladmin Shutdown
/ECT/init. d/MySQL Shutdown (the installation path of MySQL is earlier)

4. Connect to MySQL on the local machine:
Enter the MySQL/bin directory, type the mysql-uroot-p command, and press enter to enter the password.
Exit MySQL command: exit (Press ENTER)

5. Modify the MySQL password:
Mysqladmin-u username-P old Password New Password
Or enter the MySQL command line set password for root = PASSWORD ("root ");

6. Add new users. (Note: commands in the MySQL environment are followed by a semicolon as the command Terminator)
Grant select on database. * To username @ login host identified by "password"
For example, if a user's Test password is added to 123, 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 "identified by" 123 ";

Ii. MySQL database operations
You must first log on to MySQL. The related operations are performed at the MySQL prompt, and each command ends with a semicolon.

1. display the Database List.
Show databases;
2. display the data tables in the database:
Use MySQL; // open the database
Show tables;
3. display the data table structure:
Describe table name;
4. database creation:
Create Database database name;
5. Create a table:
Use Database Name;
Create Table Name (field setting list );
6. Delete databases and tables:
Drop database database name;
Drop table name;
7. Clear records in the table:
Delete from table name;
8. Display records in the table:
Select * from table name;

9. Encoding Modification
To change the encoding format of MySQL:
When MySQL is started, add the mysqld_safe command line
-- Default-character-set = GBK

To change the encoding format of a Database: Enter the command at the MySQL prompt
Alter database db_name default Character Set GBK;

Iii. Data Import and Export

1. Transfer text data to the database
Text data should conform to the format: field data is separated by the tab key, and null value is used instead. Example:
1 name duty 2006-11-23
Data Import command load data local infile "file name" into Table table name;

2. Export databases and tables
Mysqldump -- opt News> News. SQL (back up all the tables in the database news to the news. SQL file. News. SQL is a text file with any file name .)
Mysqldump -- opt news author Article> author. article. SQL (back up the author table and article table in the database news to author. article. SQL file, author. article. SQL is a text file with any file name .)
Mysqldump -- databases db1 DB2> News. SQL (back up database DBL and DB2 to the news. SQL file. News. SQL is a text file with any file name .)
Mysqldump-H host-u user-P pass -- databases dbname> file. Dump
Import the database dbname on the host named "user" and "password pass" to file. Dump.
Mysqldump -- all-databases> all-databases. SQL (back up all databases to all-databases. SQL files, a all-databases. SQL is a text file, any file name .)

3. Import Data
Mysql <all-databases. SQL (import database)
Mysql> source news. SQL; (run the MySQL command to import tables)

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 mysqlbin, then type the command mysql-uroot-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 abcd123. Enter the following command:

Mysql-h110.110.110.110-uroot-pabcd123

(Note: you do not need to add spaces for u and root. The same applies to others)

3. Exit MySQL command: exit (Press ENTER ).

Ii. Change the password

Format: mysqladmin-u username-P old Password New Password

1. Example 1: Add a password ab12 to the root user. First, enter the directory mysqlbin in DOS, and then type the following command:

Mysqladmin-uroot-Password ab12

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-uroot-pab12 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 /"/";

 

 

 

 

 

How to Create a memory table in MySQL --- convert

How to Create a memory table
?
Creating a memory table is very simple. You only need to specify engine = memory:
Create Table 'tablename' ('columnname' varchar (256) Not NUL) engine = memory default charset = Latin1 max_rows = 100000000;

 

Note:
:
When the data in the memory table is larger than the size set by max_heap_table_size, MySQL converts the data that exceeds the size and stores the data on the disk. Therefore, the performance is compromised, so we also need to adjust max_heap_table_size according to our actual situation, for example, in. in the CNF file, add the following content to [mysqld:
Max_heap_table_size = 2048 m
In addition, you can use max_rows to control the number of records in a table.

Memory tables use Hash hash indexes to store data in the memory. Therefore, it is extremely fast and suitable for caching Small and Medium databases. However, the usage is limited. The following are some of the feelings about the usage of the Blue Grass.

1. heap is visible to all users, which makes it very suitable for caching.

2. It is only suitable for use. Heap does not allow xxxtext and xxxblob data types. Only the = and <=> operators are allowed to search records (not allowed <,>, <= or> =). auto_increment is not supported; only non-empty data columns can be indexed (not null ).
Note: The operator "<=>" description: Null-safe equal. this operator and the "=" operator perform the same comparison operation. However, when both operation codes are null, the obtained value is 1 instead of null. When an operation code is null, the value is 0 instead of null.

3. Once the server is restarted, all heap table data is lost, but the heap table structure still exists because the heap table structure is stored in the actual database path and will not be automatically deleted. After the restart, the heap will be cleared. At this time, the heap query results will be empty.

4. If heap is a replicated data table, all primary key, index, auto-increment and other formats will no longer exist after the replication. You need to re-Add the primary key and index if necessary.

5. There are the following solutions for data loss caused by restart:
A. Before any query, run a simple query to check whether the heap table has data. If no data exists, write the data again, or drop the table to copy a table again. This requires more than one query. However, you can write an include file, which can be called at any time on the page that requires the heap table.
B. For the page on which the heap table is to be queried, the result of the dataset is judged for the first time and only for the first time. If the result is empty, the data needs to be written again. This saves one query.
C. A better way is to automatically write data to the heap every time MySQL restarts. However, you need to configure the server. The process is complicated and the versatility is limited.

6. Some SQL statements that may be used as expected

// Delete a table if it exists
Drop table if exists 'abc ';
// Copy the entire table XYZ to heap table ABC (including all data)
Create Table 'abc' type = heap select * From 'xyz ';
// Add the primary key ID
Alter table 'abc' add primary key ('id ');
// Add the index Username
Alter table 'abc' add index 'abc' ('username ');
From bluegrass Space