MySQLDump Shell script for backup Rumi, April 21, 2013 You can use the following script to dump mysql database and can add to crontab entry for daily backup! #!/bin/sh DAY=`/bin/date +%Y%m%d` TFILE="/backup/MYDB.$DAY.gz" mysqldump -u root -p'MYPASSWORD' MYDB | gzip > $TFILE echo "cache dump completed, dump script by rumi (hasan.emdad@mango.com.bd)" Continue Reading
MySQL Root Password Reset Rumi, March 18, 2013 First things first. Log in as root and stop the mysql daemon. Now lets start up the mysql daemon and skip the grant tables which store the passwords. mysqld_safe –skip-grant-tables You should see mysqld start up successfully. If not, well you have bigger issues. Now you should be able to… Continue Reading
MySQL Master-Master or Master-Slave Replication Error Fix Rumi, January 18, 2013 Scenario Master – Master replication MasterA is a client facing server MasterB is a warm standby server (read only) MasterB restarted abruptly and when instances were braught back up MasterA (it’s slave) was showing the following error: MasterA has the following error in show slave status: Last_IO_Errno: 1236 Last_IO_Error: Got… Continue Reading
Monitor MySQL database restore progress with pv Rumi, January 12, 2013 The pv command is one that I really enjoy using but it's also one that I often forget about. You can't get a much more concise definition of what pv does than this one: pv allows a user to see the progress of data through a pipeline, by giving information… Continue Reading
MySQLTuner adjust your MySQL database Rumi, January 6, 2013 MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability. Within seconds, it will display statistics about your MySQL installation and the areas where it can be improved. It's key to remember that MySQLTuner is a script… Continue Reading
MySQL Master-Master Replication with Heartbeat Rumi, December 5, 2012December 5, 2012 Setting Up The Two Base Systems In this setup there will be one master server (which runs the ISPConfig control panel interface) and one slave server which mirrors the web (apache), email (postfix and dovecot) and database (MySQL) services of the master server. To install the clustered setup, we need… Continue Reading
MySQL Database Import Command Rumi, July 31, 2012 The syntax is as follows to import the data created by mysqldump command: mysql -u {DB-USER-NAME} -p {DB-NAME} < {db.file.sql} mysql -u {DB-USER-NAME} -h {MySQL-SERVER-HOST-NAME} -p {DB-NAME} < {db.file.sql} In this example import a file called sales.sql for salesdb1 user and sales db, enter: $ mysql -u sales -p salesdb1… Continue Reading
MySQL Cheat Sheet Rumi, February 1, 2012 Selecting a database: mysql> USE database; Listing databases: mysql> SHOW DATABASES; Listing tables in a db: mysql> SHOW TABLES; Describing the format of a table: mysql> DESCRIBE table; Creating a database: mysql> CREATE DATABASE db_name; Creating a table: mysql> CREATE TABLE table_name (field1_name TYPE(SIZE), field2_name TYPE(SIZE)); Ex: mysql> CREATE TABLE… Continue Reading
Recover MySQL root Password Rumi, December 27, 2011 You can recover MySQL database server password with following five easy steps. Step # 1: Stop the MySQL server process. Step # 2: Start the MySQL (mysqld) server/daemon process with the –skip-grant-tables option so that it will not prompt for password. Step # 3: Connect to mysql server as the… Continue Reading
How to Allow MySQL Client to Connect to Remote MySQL server Rumi, December 19, 2011 By default, MySQL does not allow remote clients to connect to the MySQL database. If you try to connect to a remote MySQL database from your client system, you will get “ERROR 1130: Host is not allowed to connect to this MySQL server” message as shown below. $ mysql -h… Continue Reading