Mysql database installation configuration and data import/backup in Centos

1. Install and configure Mysql: the system installed in this article is Centos6.3:

The code is as follows:

Copy code

Yum list mysql # check whether there is an installation package Yum install mysql # install the mysql client Yum list mysql-server # check whether there is an installation package Yum install mysql-server # install mysql server # Start the mysql service after installation Service mysqld start # or/etc/init. d/mysqld start # Create a root administrator for mysql after startup: Mysqladmin-u root password 123456 # Then we can connect to mysql through the following command line: Mysql-u root-p # Press enter and enter the password. # View the mysql installation location Whereis mysql

2. Mysql configuration (general settings, recommended ):

The code is as follows:	Copy code
# After you log on to the system as the root user, Vi/etc/my. cnf # Add after [mysqld] Lower_case_table_names = 1 # Restart the MYSQL service. In this case, the table name is case-insensitive.

3. Data import:

The code is as follows:	Copy code
# Install Navicat for MySQL on a windows Server # Setup # Disable the firewall of the centos system # Enable mysql remote connection and manage databases in CentOS Grant all privileges on *. * to 'root' @ '%' identified by 'ebiz' with grant option; # Note: in Windows and Mac OS X, the default value of lower_case_tables_name is 1.

5. Export data under centos:

The code is as follows:	Copy code
Mysqldump-u username-p password database name> exported file name Mysqldump-u root-pebiz eblog>/home/soft/mysqlbak. SQL Export a table Mysqldump-u user name-p database name table name> exported file name Export A database structure Mysqldump-u username-p-d -- add-drop-table database name> exported file name

6. Import data:

The code is as follows:	Copy code
Create database eblog character set utf8;

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/home/soft/mysqlbak. SQL

7. Automatic backup and upload to the specified ftp for remote backup:

The code is as follows:

Copy code

Rm-rf/home/mysqlbak/*. gz # Delete the gz file Date_str = 'date + % Y % m % d' Cd/home/mysqlbak Mysqldump-h localhost-u root -- password = ebiz-R-E-e/ -- Max_allowed_packet = 1048576 -- net_buffer_length = 16384 eblog/ | Gzip>/home/mysqlbak/eblog_1_date_str. SQL .gz     Deldate = 'date-d-5day + % Y % m % d' # Delete the backup of the ftp server space five days ago Ftp-n 8.8.8.8 <EOF User ftpuser ftppassword Binary LCD/home/mysqlbak Prompt Mput eblog__str. SQL .gz Mdelete eblog_$deldate. SQL .gz Close Bye EOF

8. Set scheduled tasks

The code is as follows:	Copy code
30 4 15 ** root/home/filesbak. sh 10 3 * root/home/mysqlbak. sh

After setting, restart: service crond restart

9. mysql export and import commands:

The code is as follows:	Copy code
"D:/Program Files/MySQL/bin" -- host = server IP address -- user = username -- password = password -- add-locks -- add-drop-table -- lock-tables

-User = Username,-password = password: enter the password directly. If a special character causes a command line conflict, it can be enclosed in double quotation marks;
-Database name. You can enter multiple database names separated by spaces;
-Add-locks: in the output SQL statement, the current table is locked and unlocked before and after each segment writes data, prevents conflicts and errors caused by other operations on the database during SQL statement execution;
-Add-drop-table: Check whether the table with the same name as the current table exists before creating the table structure statement. If yes, delete the original table first and then execute the create table structure statement, otherwise, directly create the table structure;
-Lock-tables: indicates that the specified database table is locked when the mysqldump command is executed to export data;
> "Output file name. SQL: this is a Windows command line feature that outputs all content that is output to the command line window as a file to a file of the specified file name (UTF-8 encoding ).

In addition, the mysqldump command has other parameters as follows:

-No-data: only the table structure of the specified database is exported without the data part;
-No-create-info: exports only the data part of the specified database without creating the table structure;
-Add-drop-database: delete the database with the same name before creating the database.