Key behind RDS MySQL’s Cross-Region Replicas

RDS uses native MySQL binary log based replication to create read replicas. There are mainly two threads involved in MySQL replication setup i.e., IO thread and SQL thread. IO thread is responsible to connect to master and fetch the binary log events and save them as relay log files on the slave instance. SQL thread will read those events from relay logs and execute them on the slave.

The main draw back with this approach is that all the queries executed on the master by multiple connections in parallel need to be executed by single thread (SQL_thread) on the replication instance i.e., MySQL replication is single threaded by default.

In recent versions of MySQL we have feature to enable multiple threads by modifying slave_parallel_workers variable to improve the replication performance. However the time required to sync the replica depends on the write workload present on the master. Even after replica is in sync with master, If workload changes i.e., many write operations or large transactions modifying number of rows executed on master then replication lag will increase.

Replication lag is calculated from “Seconds_behind_master” value from show slave status and Seconds_Behind_Master parameter shows a huge delay in seconds. However, this can be misleading, because it only measures the difference between the timestamps of the relay log most recently executed, versus the relay log entry most recently downloaded by the IO_THREAD.

Hope this helps you in planning an efficient solution for your RDS MySQL’s Cross-Region Read-Replicas.

Happy Learning!

Aman Kharbanda


Resetting a lost MySQL root password

MySQL root password allows full access to the MySQL database and allows for all actions to be undertaken including creating new users, new databases, setting access rules and so on.

You might get the following error (Error 1045) while trying to key in the wrong password->
Enter password:
mysqldump: Got error: 1045: Access denied for user ‘root’@’localhost’ (using password: YES) when trying to connect

If you set a root password previously, but have forgotten it, you can set a new password using these steps->

1) Stop MySQL-
/etc/init.d/mysqld stop

2) Start MySQL in Safe Mode (This enables anyone to connect without a password and with all privileges. Because this is insecure, you might want to use --skip-grant-tables in conjunction with --skip-networking to prevent remote clients from connecting) –
mysqld_safe –skip-grant-tables &

3) Login without a Pwd-
mysql -u root

4) Reset the password by executing the following statement
mysql> UPDATE mysql.user SET Password=PASSWORD(“MyNewPwd”) where User=’root’;

5) Stop and Start MySQL Service-

/etc/init.d/mysqld stop

/etc/init.d/mysqld start

6) Login again using the new password and you should be good to go.

mysql -u root -p newpwd

Happy Learning!

Aman Kharbanda

Repairing MySQL Replication (Error 1396)

There are times when MySQL replication stops when you run certain updates on the master and the slave fails. If you have set up MySQL replication, then you probably know this problem. At times there are invalid MySQL queries which cause the replication to stop and you start getting these issues-

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: *******
Master_User: repl_usr
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: jprd-binlog.000047
Read_Master_Log_Pos: 521059438
Relay_Log_File: replication-relay-bin.000040
Relay_Log_Pos: 466229228
Relay_Master_Log_File: jprd-binlog.000047
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Ignore_DB: facebook,wordpress,yahoo,google
Last_Errno: 1396
Last_Error: Error ‘Operation CREATE USER failed for ‘test_usr’@’” on query. Default database: ”. Query: ‘CREATE USER ‘test_usr’@’’ IDENTIFIED BY ‘test123”
Skip_Counter: 0
Exec_Master_Log_Pos: 466229077
Relay_Log_Space: 521060120
Until_Condition: None
Until_Log_Pos: 0
Master_SSL_Allowed: No
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_SQL_Errno: 1396
Last_SQL_Error: Error ‘Operation CREATE USER failed for ‘test_usr’@’” on query. Default database: ”. Query: ‘CREATE USER ‘test_usr’@’’ IDENTIFIED BY ‘test123”
Master_Server_Id: 1
1 row in set (0.00 sec)

We have to follow the below mentioned steps to overcome such issues-
1) Skipping the Error Msg in Slave’s CNF file (Never suppress or skip all message codes).This is just to skip duplicate errors.
2) Setting the Global SQL Slave Skip Counter Value to 1.

Locate the my.cnf file, and include the parameter (–slave-skip-errors=1396) and save it again.
Next step is to Stop the slave (Stop Slave;) and then run this statement – (SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;).
Start the slave again (Start Slave;)

Verify the slave status, and you should be good now. You will notice that slave has now slowly and gradually started catching up with the Master.

For more details on Replication Slave Options and Variables, you can browse thru’ this link 

Happy learning..

Aman Kharbanda

Starting MySQL…….Manager of pid-file quit without update[FAILED]

Imagine on a Friday afternoon, when you want to leave office a bit early and you are informed of an unimaginative error such as the subjected one. Your MySQL DB Server was running smoothly and out of nowhere, you get a ticket stating MySQL Service is unavailable. There could be many reasons why the service is not starting at the first place. There was sufficient space on all the file systems so space constraint was not an issue here. Then, you need to verify if all the permissions are intact or not. Next helpline could be your error log and when you read the error log, you get following details-

InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
141106 20:41:20 InnoDB: Retrying to lock the first data file
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
141106 20:41:23 InnoDB: Shutdown completed; log sequence number 882 2873506614
141106 20:41:23 [Note] /usr/sbin/mysqld: Shutdown complete

141106 20:41:23 mysqld_safe mysqld from pid file /var/lib/mysql/*******.pid ended
141106 20:41:23 InnoDB: Started; log sequence number 882 2873506614
/usr/sbin/mysqld: File ” not found (Errcode: 2)
141106 20:41:23 [ERROR] Failed to open log (file ”, errno 2)
141106 20:41:23 [ERROR] Could not open log file
141106 20:41:23 [ERROR] Can’t init tc log
141106 20:41:23 [ERROR] Aborting
Referring to these logs, it looked to be an issue with the log or data files. I decided to delete the ib_logfile0 and ib_logfile1 files, and tried restarting MySQL Service once again but it was still throwing the same error.
(Note that once MySQL Service is restarted, these log files shall be created again).

Another area to look for is to rename your my.cnf file and try restarting the service again. It was of no luck for me.

Next, I tried renaming the data file (Be very cautious here- you need to take a backup of the original file, later rename the actual file). Voila! I was able to restart MySQL without any issues.

Hope this helps you.. Happy learning.

Aman Kharbanda

Backup and Restoration in MySQL

1. Backup a single database:

This example takes a backup of test database and dumps the output to tes.sql

# mysqldump -u root -p rootpassword test > test.sql
# mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

2. Backup multiple databases:

# mysqldump -u root -ptmppassword –databases test1 test2 > test1_test2.sql

Verify the test1_test2.sql dumpfile contains both the database backup.

# grep -i “Current database:” /tmp/test1_test2.sql
— Current Database: ‘mysql’
— Current Database: ‘test1’

3. Backup all the databases:

The following example takes a backup of all the database of the MySQL instance.

# mysqldump -u root -ptmppassword –all-databases > /tmp/all-database.sql

4. Backup a specific table:

In this example, we backup only the accounts table from test database.

# mysqldump -u root -ptmppassword test accounts \ > /tmp/test_accounts.sql

How To Restore MySQL database

1. Restore a database

In this example, to restore the sugarcrm database, execute mysql with < as shown below. When you are restoring the dumpfilename.sql on a remote database, make sure to create the sugarcrm database before you can perform the restore.

# mysql -u root -ptmppassword

mysql> create database sugarcrm;
Query OK, 1 row affected (0.02 sec)

# mysql -u root -ptmppassword sugarcrm < /tmp/sugarcrm.sql

# mysql -u root -p[root_password] [database_name] < dumpfilename.sql

2. Backup a local database and restore to remote server using single command:

This is a sleek option, if you want to keep a read-only database on the remote-server, which is a copy of the master database on local-server. The example below will backup the sugarcrm database on the local-server and restore it as sugarcrm1 database on the remote-server. Please note that you should first create the sugarcrm1 database on the remote-server before executing the following command.

[local-server]# mysqldump -u root -ptmppassword sugarcrm | mysql \
-u root -ptmppassword –host=remote-server -C sugarcrm1
[Note: There are two — (hyphen) in front of host]

Privileges, User Lists, Password Change Script for MySQL

Show privileges In MySQL :-

mysql> SHOW GRANTS FOR ‘root’@’localhost’;
| Grants for root@localhost |

To list the privileges granted to the account that you are using to connect to the server, you can use any of the following statements:


To get more knowledge and details on privileges->


List of MYSQL users and their privileges –>

mysql> select user,host from mysql.user;
| user | host |
| root | % |
| root | |
| debian-sys-maint | localhost |
| root | localhost |
| root | stack.domain |
8 rows in set (0.01 sec)
The above output shows a list of existing MySQL accounts. Note that a MySQL account has two components: user and host. This allows the same user to use different MySQL accounts depending on which host they are connecting from. “%” is a wildcard character interpreted as “any” host.

To find the privilege(s) granted to a particular MySQL account:

mysql> show grants for ‘root’@’%’;
| Grants for root@% |
| GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ …. |
1 row in set (0.00 sec)

To change the password of a given account:

SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘Test@1234’);

Create User in MySQL:
CREATE USER ‘test_user’@’localhost’ IDENTIFIED BY ‘XXXX’;

Grant Privileges to user:
grant select on *.* to ‘new_user’@’%’ identified by ‘xxxxx’ with grant option;

After granting privileges, use the below command to bring that in effect-
mysql> flush privileges;

DB Size and Free Space Availability Script for MySQL

Script to check MySQL DB size in MB’s->

SELECT table_schema “Data Base Name”, sum( data_length + index_length ) / 1024 / 1024 “Data Base Size in MB”
FROM information_schema.TABLES GROUP BY table_schema ;

Script to view the Free space available for my DB in MySQL ->

SELECT table_schema “Data Base Name”,
sum( data_length + index_length ) / 1024 /
1024 “Data Base Size in MB”,
sum( data_free )/ 1024 / 1024 “Free Space in MB”
FROM information_schema.TABLES
GROUP BY table_schema ;