How to operate databases using Linux commands

For example, to back up an existing mysql database named linux, run the mysqldump command in the following format: [root @ linuxidcroot] # mysqldump-uro

For example, to back up an existing mysql database named linux, run the mysqldump command in the following format: [root @ linuxidc root] # mysqldump-u ro

For example, to back up an existing mysql database named linux, run the mysqldump command.
The command format is as follows:
[Root @ linuxidc root] # 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 obtained by yourself, or the path can be arranged by yourself. For example, if I want to back up the linux database to/home/beinan and the database file name is linuxsir031130. SQL, enter the following command.

[Root @ linuxidc root] # mysqldump-u root-p linux>/home/beinan/linuxsir031130. SQL
Enter password: Enter the database password of the database administrator root.

If the data volume occupies a large space, you can use gzip to compress the data. The command is as follows:

# Mysqldump-u root-p tm | gzip> tm_050519. SQL .gz

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.

Recovery:
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 @ linuxidc root] # 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.

How can I export data from a database table? (Unlike backups)

# Mysql

(# Mysql> use linux;) # optional

# Mysql> select * from linux. table-name into outfile "/home/table-name.txt ";

#/Home/table-name.txt may report an error if you remove/home””table-name.txt"

Saved in/var/lib/mysql/table-name.txt by default

A supplement to common 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 @ linuxidc root] #/opt/mysql/share/mysql. server stop

Then execute
[Root @ linuxidc 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 @ linuxidc root] #/opt/mysql/bin/myisamchk-r/opt/mysql/var/linux/*. MYI

7] mysqlshow command: displays the database and table selected by the user
[Root @ linuxidc root] #/opt/mysql/bin/mysqlshow-uroot-p [database name]

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

[Root @ linuxidc root] #/opt/mysql/bin/mysqlshow-uroot-p linux