mtheDBA

Load data infile will read the content from the text file and insert into table. This process is very fast in MySQL. Below is the Syntax suggested by MySQL in their documentation: LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE ' file_name ' [REPLACE | IGNORE] INTO TABLE tbl_name [PARTITION ( partition_name ,...)] [CHARACTER SET charset_name ] [{FIELDS | COLUMNS} [TERMINATED BY ' string '] [[OPTIONALLY] ENCLOSED BY ' char '] [ESCAPED BY ' char '] ] [LINES [STARTING BY ' string '] [TERMINATED BY ' string '] ] [IGNORE number {LINES | ROWS}] [( col_name_or_user_var ,...)] [SET col_name = expr ,...] Will look into some of the procedures to load data into table using an external text file: Procedure 1: Text file contains header: LOAD DATA INFILE ‘path/to/example.csv’ INTO TABLE example FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ IGNORE ...

In the earlier post we discuss about how to take backup using mysqldump. Today will discuss about how to take backup using Percona XtraBackup. The Percona XtraBackup is a Physical Dump / Backup which consists of raw copies of data and data directories present in it. This type of backup is very useful and suitable for the databases which contains large and important data sets that need to recovered quickly when the disaster occurs. Creating a Backup: To create a backup, Xtrabackup script has to be called with backup, datadir and targetdir parameters. backup - is to create the backup datadir - which is where the MySQL data directory stored targetdir - backup directory, where we want to save the backup If the target directory does not exist,  xtrabackup  creates it. If the directory does exist and is empty,  xtrabackup  will succeed.  xtrabackup  will not overwrite existing files, it will fail with operating system error 17,  file   exists ...

mysqldump is a command to dump all the data into a SQL file from a Database, which is also called as logical backup, here the server will interact with database information and convert the data into logical form, usually the process is very slow when compared with Physical Backup. In this post, we will look into how to take a backup of tables, stored procedures and views in a MySQL Database. How to take backup of Tables: mysqldump - u username - p password --single-transaction databasename > tables_dump.sql How to take backup of Stored Procedures: mysqldump - u username - p password routines --no-create-info --no-data --no-create-db --single_transaction databasename > routines_dump.sql How to take backup of Views: mysqldump creates the views as tables in the exported sql file, the problem with this is while importing this backup to the database all the views will be created as Tables instead of Views. mysqldump doesn't support taking backup of views. B...