Mysql commands in linux to back up databases

Mysql in linux Command backup database

If mysql is installed on the machine, enter mysql directly.   1: commands for opening a database Mysql> use mysql Database changed 2: view database commands Mysql> show databases; 3: view the table commands in the database Mysql> show tables; 4: view the detailed structure of the table Mysql> desc tablename; 5. Create a database Mysql> create database school; Query OK, 1 row affected (0.00 sec) 6. Create a table Mysql> create table user01 ( -> Id varchar (20) not null, -> UserName varchar (10) not null, -> Age int (11) default '0 ', -> Sex char (2) not null default 'M ', -> Primary key (id) ->) TYPE = InnoDB; Query OK, 0 rows affected, 1 warning (0.02 sec) mysql> desc student; 7: insert Mysql> insert into student (id, stuName) values ('1', 'tomcat '); Query OK, 1 row affected (0.00 sec) 8: Delete Mysql> delete from student where id = '1 '; Query OK, 1 row affected (0.01 sec) 9: delete all data in the table Mysql> truncate table student; Query OK, 1 row affected (0.01 sec) 10: Create a new user and grant permissions Mysql> grant all privileges on *. * to dbsync @ "127.0.0.1" identified by "1234 "; 11. Change the Mysql user password C: \ Mysql5.0 \ bin> mysqladmin-u root-p password 1234 Enter password :**** Back up databases and tables We use the mysqldump command to back up the database. C: \ mysql \ bin \> mysqldump-u root-p 3306 mysql> d: \ backup. SQL Execute this statement to back up mydb to the backup. SQL file of drive D. Back up multiple database tables C: \ mysql \ bin \> mysqldump-u root-p 3306 school user01 user> d: \ backup. SQL The user01 table and user table content and table definitions in the school database are backed up to the backup. SQL file on the d disk. Back up all databases C: \ myql \ bin> mysqldump-u root-p 3306-all-database> d: backup. SQL Restore a Mysql database C: \ mysql \ bin \ mysql-u root-p 3306 school Restore one of the tables Mysql> source d: \ books. SQL; Back up the database should be in the normal command input place. Do not enter the mysql command input box. Input mysqldump-uroot-p mdwk>/usr/mysqlback/mdwk2010-07-09. SQL; then you can 1 mysqlldump command Mysqldump adopts an SQL-level backup mechanism. It imports data tables into SQL script files and is suitable for upgrading between different MySQL versions. This is also the most common backup method. Using mysqldump for backup is very simple. to back up the database "db_backup", run the following command: #mysqldump –u -p phpbb_db_backup > /usr/backups/mysql/db_backup2008-1-6.sql You can also use the gzip command to compress the backup file: #mysqldump db_backup | gzip > /usr/backups/mysql/ db_backup2008-1-6.sql.gz Back up only some frequently updated database tables: ## mysqldump sample_db articles comments links > /usr/backups/mysql/sample_db.art_comm_lin.2008-1-6.sql The preceding command backs up the articles, comments, and links tables. Run the following command to restore data: #mysql –u -p db_backup </usr/backups/mysql/ db_backup2008-1-6.sql Note that you must ensure that the database is running when using this command. 2. Use the SOURCE syntax In fact, this is not a standard SQL syntax, but a function provided by the mysql client, for example: # SOURCE /tmp/db_name.sql; Here, you must specify the absolute path of the file and the file must be read by the mysqld running user (such as nobody. 3 mysqlhotcopy backup Mysqlhotcopy can only be used to back up MyISAM and can only run on linux, Unix, and NetWare systems. Mysqlhotcopy supports copying multiple databases at a time and regular expression. The following are examples: #mysqlhotcopy -h=localhost -u=goodcjh -p=goodcjh db_name /tmp (Copy the database directory db_name to/tmp) Note: To use mysqlhotcopy, you must have the SELECT and RELOAD permissions (to execute flush tables) and be able to read the datadir/db_name directory. Restoration method: Mysqlhotcopy backs up the entire database Directory, which can be copied directly to the directory specified by mysqld (in this example,/usr/local/mysql/data, at the same time, pay attention to permission issues. In addition, delete the old database copy as follows: # /bin/rm -rf /mysql-backup/*old To disable the mysql server, copy files, and query the three steps for starting the mysql server: