Archive for the ‘MySQL FAQ’ Category

How to Find and Replace Text in MySQL Database using SQL

MySQL database has a handy and simple string function REPLACE() that allows table data with the matching string (from_string) to be replaced by new string (to_string). This is useful if there is need to search and replace a text string which affects many records or rows, such as change of company name, postcode, URL or spelling mistake.

The syntax of REPLACE is REPLACE(text_string, from_string, to_string)

MySQL reference describes REPLACE as function that returns the string text_string with all occurrences of the string from_string replaced by the string to_string, where matching is case-sensitive when searching for from_string. text_string can be retrieved from the a field in the database table too. Most SQL command can be REPLACE() function, especially SELECT and UPDATE manipulation statement.

For example:

update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, ‘find this string’, ‘replace found string with this string’);

update client_table set company_name = replace(company_name, ‘Old Company’, ‘New Company’)

The above statement will replace all instances of ‘Old Company’ to ‘New Company’ in the field of company_name of client_table table.

Another example:

SELECT REPLACE(‘www.mysql.com’, ‘w’, ‘Ww’);

Above statement will return ‘WwWwWw.mysql.com’ as result.

Check and Optimize MySQL Database Automatically with Crontab/Cron

MySQL is a very popular free yet powerful database system. But even in great databases, the tables may get fragmented with overhead due to continuous update, or delete and insert operation on data stored in database. Beside, it’s also possibility that the databases may get corrupted. Thus, performing health check on database and optimize MySQL server regularly is an important task.

It’s a bit troublesome if the database administrators have to login to the server or launching the phpMyAdmin to optimize the databases one by one or table by table manually. Sometimes DB admin may simply forgets to do the job or set the frequency of optimization to lesser times. It’s recommended that every tables in MySQL databases are checked at least once a day on a busy server.

It’s possible to automate the optimization of MySQL process by using crontab function in Linux/Unix/CentOS/FreeBSD. The cron job to check and optimize MySQL databases can be created by using mysqlcheck client utility comes MySQL installation. mysqlcheck client can checks, repairs, optimizes, and analyzes tables in MySQL database.

To create a new cron job, login to the server as root or any other user, and then edit the crontab file (in most operating system, crontab -e will open crontab file in default text editor) to add in the following line of text. For users using cPanel, click on “Cron job” where you can set up crontab at daily, hourly and other interval. Experience webmasters can also set up a crontab file in rc.hourly or rc.daily or other cron directory. Note that if you login as a MySQL or normal user with no access privileges to all database, it’s not possible to optimize all databases, unless user ID and password for root is specified as in example below.

0 1 * * * mysqlcheck -Aao –auto-repair -u root -p[password] > /dev/null

The above statement has the syntax similar to “mysqlcheck [options] –all-databases”, where –all-databases parameter is the default action is no databases is specified thus can be omitted. The command will run mysqlcheck client to automatically analyze and optimize all databases at 1 am everyday. Note that there is not space between -p and your password for root. You can change the running time to your preference, and also change the options for mysqlcheck command. If you just want to check and optimize certain databases or certain tables without the database, use the following syntax:

mysqlcheck [options] db_name [tables]
mysqlcheck [options] –databases DB1 [DB2 DB3…]

You may want to remove –auto-repair switch from the above command, as a table repair operation might cause data loss under some circumstances the operation due to causes include but are not limited to filesystem errors. For those who has changed the character set and collation of MySQL databases may also need to use –default-character-set option. More information on all available switches and options available can be found here.

How to Backup and Restore (Export and Import) MySQL Databases Tutorial

phpMyAdmin can be used to export or backup MySQL databases easily. However, if the database size is very big, it probably won’t be a good idea. phpMyAdmin allows users to save database dump as file or display on screen, which involves exporting SQL statements from the server, and transmitting the data across slower network connection or Internet to user’s computer. This process slow the exporting process, increase database locking time and thus MySQL unavailability, slow the server and may simply crash the Apache HTTPD server if too many incoming web connections hogging the system’s resources.

The better way to backup and export MySQL database is by doing the task locally on the server, so that the tables’ data can be instantly dumped on the local disk without delay. Thus export speed will be faster and reduce the time MySQL database or table is locked for accessing. This tutorial is the guide on how to backup (export) and restore (import) MySQL database(s) on the database server itself by using the mysqldump and mysql utilities. There are basically two methods to backup MySQL, one is by copying all table files (*.frm, *.MYD, and *.MYI files) or by using mysqlhotcopy utility, but it only works for MyISAM tables. Below tutorial will concentrate on mysqldump which works for both MyISAM and InnoDB tables.

How to Export or Backup or Dump A MySQL Database

To export a MySQL database into a dump file, simply type the following command syntax in the shell. You can use Telnet or SSH to remotely login to the machine if you don’t have access to the physical box.

mysqldump -u username -ppassword database_name > dump.sql

Replace username with a valid MySQL user ID, password with the valid password for the user (IMPORTANT: no space after -p and the password, else mysqldump will prompt you for password yet will treat the password as database name, so the backup will fail) and database_name with the actual name of the database you want to export. Finally, you can put whatever name you like for the output SQL dump file, here been dump.sql.

The while data, tables, structures and database of database_name will be backed up into a SQL text file named dump.sql with the above command.

How to Export A MySQL Database Structures Only

If you no longer need the data inside the database’s tables (unlikely), simply add –no-data switch to export only the tables’ structures. For example, the syntax is:

mysqldump -u username -ppassword –no-data database_name > dump.sql

How to Backup Only Data of a MySQL Database

If you only want the data to be backed up, use –no-create-info option. With this setting, the dump will not re-create the database, tables, fields, and other structures when importing. Use this only if you pretty sure that you have a duplicate databases with same structure, where you only need to refresh the data.

mysqldump -u username -ppassword –no-create-info database_name > dump.sql

How to Dump Several MySQL Databases into Text File

–databases option allows you to specify more than 1 database. Example syntax:

mysqldump -u username -ppassword –databases db_name1 [db_name2 …] > dump.sql

How to Dump All Databases in MySQL Server

To dump all databases, use the –all-databases option, and no databases’ name need to be specified anymore.

mysqldump -u username -ppassword –all-databases > dump.sql

How to Online Backup InnoDB Tables

Backup the database inevitable cause MySQL server unavailable to applications because when exporting, all tables acquired a global read lock using FLUSH TABLES WITH READ LOCK at the beginning of the dump until finish. So although READ statements can proceed, all INSERT, UPDATE and DELETE statements will have to queue due to locked tables, as if MySQL is down or stalled. If you’re using InnoDB, –single-transaction is the way to minimize this locking time duration to almost non-existent as if performing an online backup. It works by reading the binary log coordinates as soon as the lock has been acquired, and lock is then immediately released.

Syntax:

mysqldump -u username -ppassword –all-databases –single-transaction > dump.sql

How to Restore and Import MySQL Database

You can restore from phpMyAdmin, using Import tab. For faster way, upload the dump file to the MySQL server, and use the following command to import the databases back into the MySQL server.

mysql -u username -ppassword database_name < dump.sql

The import and export of MySQL database not only is important to recover the data when disaster strikes, but also provides an easy way to migrate or move to another server, such as when switching web hosting providers. However, do note that one common problem – character set encoding. Newer release of mysqldump uses UTF8 as its default charset if nothing is specified, while older versions (older than 4.1 typically) use Latin1 as default characterset. If you database charset is Latin1 and dump in UTF8 collation, the data may ends up become simply rubbish, garbled, or unreadable (frequently happen with WordPress blog). If this case, use –default-character-set=charset_name option to specify the character set or convert the database to UTF8.