Linux MySQL Service

1. How to create a management user for the mysqld database?

After the database is installed, we should create a management account for the mysql database. To set the root user as the administrator, we should run the following command;

[Root @ linuxsir01 root] #/opt/mysql/bin/mysqladmin-u root password 123456
[Root @ linuxsir01 root] #

The above command shows that the mysql database administrator is root and the password is 123456.

2. How do I access the mysql database? Take mysql database administrator root and password 123456 as an example;

[Root @ linuxsir01 root] #/opt/mysql/bin/mysql-u root-p 123456

After the preceding command is output, the following prompt is displayed;

Welcome to the MySQL monitor. Commands end with; or/g.
Your MySQL connection id is 6 to server version: 3.23.58

Type 'help; 'or'/H' for help. type'/C' to clear the buffer.

Mysql>

Note: When operating these commands, you should open the mysqld server. These new siblings have long known about it :)

3. How to operate commands in the database? I think this is all in the mysql manual. I mainly want to pay attention to it. In fact, I cannot have several commands. If you want to learn mysql, it is not difficult. If you have been operating mysql in windows, it is actually the same here. mysql is a cross-platform database, and its usage is the same.

In the mysql database, each command is ended with ";". Some new siblings may have forgotten to enter the; the result cannot be returned. :):)

1. Check which databases are available in MySQL?

Code: mysql> show databases; ---------- | database | ---------- | MySQL | test | ---------- 2 rows in SET (0.00 Sec) mysql>
After MySQL is installed and the Administrator is set up, the first time we enter the system, we use the show databases; command to view the Database List, we found that there are two databases, MySQL and test, which are self-built by the system, it is intended for everyone to practice.

4. How to create and delete a database?

For example, to create a database named Linux, run the following command:

Mysql> Create Database [database name];

Therefore, we should run the following command to create a database named Linux

Mysql> Create Database Linux;
Query OK, 1 row affected (0.00 Sec)

Is it built ?? It must have been built, because there are OK :)

Check whether there is a Linux database?

Code: mysql> show databases; ---------- | database | ---------- | Linux | MySQL | test | ---------- 3 rows in SET (0.00 Sec) mysql>
How can we delete a database ??
Mysql> drop database [database name];

For example, to delete the created linux database, use the following command;
Mysql> drop database linux;
Query OK, 0 rows affected (0.00 sec)

Is it deleted ??

Code: mysql> show databases; ---------- | Database | ---------- | mysql | test | ---------- 2 rows in set (0.00 sec) mysql>

5. There are many questions about how to operate a database. I suggest you read the mysql manual. There are too many things in it. To operate a database, you must first specify a database as the current database. use the use command

Mysql> use [database];

For example, if I want to specify the database linux as the current database, it should be

Mysql> use linux;
Database changed
Mysql>

 

6. How to back up the database ??

For example, to back up an existing mysql database named linux, run the mysqldump command.

The command format is as follows:

[Root @ linuxsir01 root] #/opt/mysql/bin/mysqldump-u root-p linux>/root/linux. SQL
Enter password: Enter the Database password here

Through the above command, we need to understand two things: first, back up the database as a database administrator; second, the backup destination is/root, and the backup file name is linux. SQL. In fact, the backup location and file name are determined based on your own situation. The file name can be retrieved by yourself, or the path can be arranged by yourself;

For example, if I want to back up a linux database to/home/beinan and the database name is linuxsir031130. SQL, enter the following command.
[Root @ linuxsir01 root] #/opt/mysql/bin/mysqldump-u root-p linux>/home/beinan/linuxsir031130. SQL
Enter password: Enter the database password of the database administrator root.

In this way, we can find the backup file linuxsir031130. SQL for the database named linux in mysql under the/home/beinan directory.

To sum up, we must learn to make changes when learning. :):)

5. How can I import the backup database to the database?

First, we need to perform the preceding operations, such as adding a Database Administrator (if you have not added a mysql database administrator) and creating a database.

For example, to back up linuxsir031130. SQL IN THE/home/beinan directory and import it to a database named linux, perform the following operations;

[Root @ linuxsir01 root] #/opt/mysql/bin/mysql-u root-p linux Enter password: Enter the password here

If the machine is good, the database is relatively small, just a few minutes.

6. other commonly used mysql commands;

View status
Mysql> show status;

View Processes

Code: mysql> show processlist; ---- ------ ----------- ------ --------- ------ ------- ---------------- | Id | User | Host | db | Command | Time | State | Info | ---- ------ ----------- ------ --------- ------ ------- ------------------ | 16 | root | localhost | NULL | Query | 0 | NULL | show processlist | ---- ------ ----------- ------ --------- ------ ------- ------------------ 1 row in set (0.00 sec) mysql>
To view a table, you must first specify a database as the current database; for example, a database named linux;

Mysql> use linux;
Mysql> show tables;
Empty set (0.00 sec)

Mysql>

7. Add some frequently-used mysql database commands;

Several common mysql-related management commands

Mysql command: displays and uses the mysql database in basic text. I have mentioned the usage in the previous section, such as logon.

Mysqladmin command, used to create and maintain the mysql database, which has been briefly mentioned earlier;

Isamchk is a database file used to repair, check, and optimize the. ISM suffix;

Mysqldump is used to back up the database, which has been described earlier;

Myisamchk is used to fix database files with the. myi suffix;

For example, to check whether there is a problem with the database named Linux. myi database table, use the following command;

Stop the mysqld Server
[Root @ linuxsir01 root] #/opt/MySQL/share/MySQL. server stop

Then execute
[Root @ linuxsir01 root] #/opt/MySQL/bin/myisamchk/opt/MySQL/var/Linux/*. myi

The command above means to check all. myi files. The database directory is in the/opt/MySQL/var/Linux/directory.

If any problem occurs, use the-R parameter to fix it.
[Root @ linuxsir01 root] #/opt/MySQL/bin/myisamchk-r/opt/MySQL/var/Linux/*. myi

8. mysqlshow command: displays the database and table selected by the user.

[Root @ linuxsir01 root] #/opt/MySQL/bin/mysqlshow-u root-P [database name]

For example, if I want to view a database named Linux, it should be:

[Root @ linuxsir01 root] #/opt/MySQL/bin/mysqlshow-u root-P Linux

 

========================================================== ========================================

Delete Database
Drop database db_name;

Display table structure
Show columns from table_name;

Delete table
Drop table table_name

Export data tables
Mysqldump-u root-p database_name table_name of database_name> sentence_of_ SQL

Restore permissions to users
Grant all privileges on database_name to database_user;

Clear Data Tables
Truncate table table_name

Change the name of a data table
Alter table old_table_name rename new_table_name

Describe the table structure
Describe table_name;

Execute the mysql statement without entering the database.
Mysql-u username-pusername database-e "msyql excute sectence ";

Update database information.
Update tablename set Column = Column_value where Column = Column_value

Number of statistical data rows
SELECT count (*) FROM tablename

1. Export the entire database
Mysqldump-u username-p Database Name> exported file name
Mysqldump-u wcnc-p smgp_rj_wcnc> wcnc. SQL

2. Export a table
Mysqldump-u user name-p database name Table Name> exported file name
Mysqldump-u wcnc-p smgp_rj_wcnc users> wcnc_users. SQL

3. Export a database structure
Mysqldump-u wcnc-p-d -- add-drop-table smgp_apps_wcnc> d: wcnc_db. SQL
-D no data -- add-drop-table adds a drop table before each create statement.

4. Import the database
Common source commands
Go to the mysql Database Console,
For example, mysql-u root-p

Mysql> use Database
Then run the source command. The following parameter is the script file (for example,. SQL used here)
Mysql> source d: wcnc_db. SQL

MySql root Password Reset Solution

1. Stop the running MySQL process
In Linux, run killall-TERM mysqld
In Windows, if it is written as a service, you can run: net stop mysql. If it is not loaded as a service, you can directly close it in the Process Manager.

2. Start MySQL in Safe Mode
Run/usr/local/mysql/bin/mysqld_safe -- skip-grant-tables &
In Windows, run X:/MySQL/bin/mysqld-nt.exe -- skip-grant-tables on the command line

3. You will be able to access MySQL without a password.
In Linux, run/usr/local/mysql/bin/mysql-u root-p to enter
In Windows, run X:/MySQL/bin/mysql-u root-p to enter

4. Change the password
Use mysql
Update user set password = password ("new password") where user = "root ";
Flush privileges;
Note: For version 4.1 or later, use the old_password () function to change the password.

Fuzzy match in SQL statements
Select * from talbe_name where column_name like "% _ like _ %"

Delete from table_name where column_name like "% _ like _ %"