How to reset MySQL Root Password in Linux


First, Let’s stop the mysql service, In Red Hat’s family (Red Hat Enterprise, Fedora, CentOS) the Mysql Daemon is called ‘mysqld‘, but in Debianlike Ubuntu based distributions and OpenSuse its called ‘mysql‘. I’ll be using the debian based naming
sudo service mysql stop
sudo /etc/init.d/mysql stop
Now, we’ll start the server in safe mode and skip grant tables so that it wont ask for the password we had lost when we try to login Continue reading “How to reset MySQL Root Password in Linux” »

Importing Big mysqldump with Progress Bar PV

I am using CentOS 6.4 box, so it requires me to install EPEL repo at the first place:

$ rpm -Uhv

Install pv using yum:

$ yum install -y pv

Installation done. Let the importing begin!

$ pv /home/user/my_big_dump.sql | mysql -uroot -p

For Ubuntu/Debian distribution intall PV using-

apt-get install pv



MySQLDump Shell script for backup

You can use the following script to dump mysql database and can add to crontab entry for daily backup!


DAY=`/bin/date +%Y%m%d`
mysqldump -u root -p'MYPASSWORD' MYDB | gzip > $TFILE
echo "cache dump completed, dump script by rumi ("


MySQL Root Password Reset

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 connect to mysql without a password.

mysql –user=root mysql
update user set Password=PASSWORD('new-password') where user='root';
flush privileges;

Now kill your running mysqld, then restart it normally. You should be good to go. Try not to forget your password again.


MySQL Master-Master or Master-Slave Replication Error Fix

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 fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’


Slave: stop slave;

Master: flush logs

Master: show master status; — take note of the master log file and master log position


Slave: start slave;

Read more:



Monitor MySQL database restore progress with pv

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 such as time elapsed, percentage completed (with progress bar), current throughput rate, total data transferred, and ETA.

The usage certainly isn't complicated:

To use it, insert it in a pipeline between two processes, with the appropriate options. Its standard input will be passed through to its standard output and progress will be shown on standard error.

A great application of pv is when you're restoring large amounts of data into MySQL, especially if you're restoring data under duress due to an accidentally-dropped table or database. (Who hasn't been there before?) The standard way of restoring data is something we're all familiar with:

# mysql my_database < database_backup.sql

The downside of this method is that you have no idea how quickly your restore is working or when it might be done. You could always open another terminal to monitor the tables and databases as they're created, but that can be hard to follow.

Toss in pv and that problem is solved:

# pv database_backup.sql | mysql my_database
96.8MB 0:00:17 [5.51MB/s] [==> ] 11% ETA 0:02:10

When it comes to MySQL, your restore rate is going to be different based on some different factors, so the ETA might not be entirely accurate.



MySQLTuner adjust your MySQL database

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 which can assist you with your server, but it is not the solution to a badly performing MySQL server. The best performance gains come from a thorough review of the queries sent to the server, and an evaluation of the MySQL server itself. A qualified developer in your application's programming or scripting language should be able to work with a MySQL database administrator to find improvements for your server. Once the server and application are optimized well, you may need to consider hardware upgrades to the physical server itself.
This is a really useful tool for helping to optimize MySQL performance.Understanding the various my.cnf variables and how they affect performance can seem really complicated but this tool takes some of the pain away and makes it easier to understand the effects of each variable.It is especially useful to be able to see the global memory usage, memory usage per thread and the maximum possible memory usage — that is really valuable information that is otherwise complex to calculate.

Run MySQLTuner in Debian

Copy and past the below code and save it as using nano 😉 Continue reading “MySQLTuner adjust your MySQL database” »


MySQL Master-Master Replication with Heartbeat

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 two servers with a Debian 6.0 minimal install.

Install only steps 1 – 8 of the perfect server tutorial and not the other steps as they differ for a clustered setup!

In my example I use the following hostnames and IP addresses for the two servers:

Master Server

Hostname: server1.example.tld

Slave server

Hostname: server2.example.tld

Whereever these hostnames or IP addresses occur in the next installation steps you will have to change them to match the IP's and hostnames of your servers.

It is a good idea to synchronize the system clock with an NTP (network time protocol) server over the Internet. Simply run Continue reading “MySQL Master-Master Replication with Heartbeat” »


MySQL Database Import Command

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 < sales.sql

If database salesdb1 does not exists, first create it and than import it as follows:

$ mysql -u root -p -e 'create database salesdb1'
$ mysql -u sales -p salesdb1 < sales.sql


MySQL Cheat Sheet

Selecting a database:

mysql> USE database;

Listing databases:


Listing tables in a db:


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 pet (name VARCHAR(20), sex CHAR(1), birth DATE);

Load tab-delimited data into a table:

mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table_name;
(Use \n for NULL)

Inserting one row at a time:

mysql> INSERT INTO table_name VALUES ('MyName', 'MyOwner', '2002-08-31');
(Use NULL for NULL)

Retrieving information (general):

mysql> SELECT from_columns FROM table WHERE conditions;
All values: SELECT * FROM table;
Some values: SELECT * FROM table WHERE rec_name = "value";
Multiple critera: SELECT * FROM TABLE WHERE rec1 = "value1" AND rec2 = "value2";

Reloading a new data set into existing table:

mysql> SET AUTOCOMMIT=1; # used for quick recreation of table
mysql> DELETE FROM pet;
mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table;

Fixing all records with a certain value:

mysql> UPDATE table SET column_name = "new_value" WHERE record_name = "value";

Selecting specific columns:

mysql> SELECT column_name FROM table;

Retrieving unique output records:

mysql> SELECT DISTINCT column_name FROM table;


mysql> SELECT col1, col2 FROM table ORDER BY col2;
Backwards: SELECT col1, col2 FROM table ORDER BY col2 DESC; Continue reading “MySQL Cheat Sheet” »