Mysql import and export include functions or stored procedures _ MySQL

Mysql import and export include functions or stored procedures bitsCN.com

Mysql import and export includes functions or stored procedures

1. mysql exports the entire database

Mysqldump-h hostname-u username-p databasename> backupfile. SQL

If the root user does not use the password, he can not write-p. of course, you can create a path for the exported SQL file. if it is not specified, it will be stored in the bin directory of mysql.

2. mysql exports a database table

Mysqldump-hhostname-uusername-ppassword database tablename> exported file name

Mysqldump-hlocalhost-uroot hqgr t_ug_user> user. SQL

3. mysql exports a database structure

Mysqldump-hhostname-uusername-ppassword-d -- add-drop-table databasename> d: hqgrstructure. SQL

-D no data -- add-drop-table adds a drop table before each create statement.

4. if you need to export functions or stored procedures in mysql

Mysqldump-h hostname-u username-ntd-R databasename> backupflie. SQL (including full export of stored procedures and functions at a time)

-Ntd indicates the structure and data of the exported table;-R indicates the export function and stored procedure.

For more information, see mysqldump -- help.

Commonly used mysql data import commands:

Mysql database import and export:

Mysqldump-u username-p database name> database name. SQL

For example:

Mysqldump-u root-p testdb> testdb. SQL (excluding stored procedures and functions)

Mysqldump-u root-p-R testdb> testdb. SQL (** including stored procedures and functions **)

The MySQL source command imports data into the database:

Mysql> use testdb;

Mysql> set names utf8;

Mysql> source/tmp/bdc. SQL;

Handle strange errors:

The following code exports the stored procedure:

1 # mysqldump-u database username-p-n-t-d-R database name> file name

-D indicates -- no-create-db,-n indicates -- no-data,-t indicates -- no-create-info, and-R indicates exporting function and procedure. Therefore, the above code only exports functions and stored procedures, and does not export table structures and data. However, the exported content contains the trigger. When importing data to mysql, the following error occurs:

ERROR 1235 (42000) at line **: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

Therefore, trigger must be disabled during export. The code is

1 # mysqldump-u database username-p-n-t-d-R -- triggers = false database name> file name

In this way, a new problem occurs:

ErrorCode: 1418

This function has none of DETERMINISTIC, NOSQL, or reads SQL DATA inits declaration and binary logging is enabled (you * might * want to use the less safe log_bin_trust_function_creators variable)

The solution is to find [mysqld] in/etc/my. cnf and add the following line under it:

1 log-bin-trust-function-creators = 1

Create database: create database 'total _ admin' default character set utf8 COLLATE utf8_general_ci;

1. View mysql size

Use database name SELECT sum (DATA_LENGTH) + sum (INDEX_LENGTH) FROM information_schema.TABLES where TABLE_SCHEMA = 'database name ';

The result is in bytes, except 1024 for K and 1048576 for M.

2. view the last mysql modification time of the table. select TABLE_NAME, UPDATE_TIME from INFORMATION_SCHEMA.tables where TABLE_SCHEMA = 'database name ';

BitsCN.com