Mysql Replication backup and restoration failed multipal time

linuxMySQLmysql-5.5mysqldumpreplication

I tried restoring MySQL backup several time but unfortunately it gets failed by some or the new reason.

I have 2 host Master & Slave with below details.

Server1_Master
os-centos6.7
mysql- 5.6.28-2.el6

Server2_Slave
os- centos6.6
mysql- 5.5.41

Engine: innodb (on both host)

Below are the respected step used by me.

Master Backup

mysqldump -uroot -p –skip-lock-tables –single-transaction –flush-logs –hex-blob –master-data=2 -A > ~/dump.sql

Restoring mysql on slave

mysql -u root -p < /location/dump.sql

CHANGE MASTER TO MASTER_HOST='<>',MASTER_USER='replicant',MASTER_PASSWORD='<>', MASTER_LOG_FILE='<>', MASTER_LOG_POS=<>;

START SLAVE;

Now the new error which we am getting after server hrs of restoration is below

"ERROR 1449 (HY000) at line 150536: The user specified as a definer ('lipl_ga_app'@'%') does not exist "

Please let me know the best way to start new replication.

Best Answer

The error itself is happening because there is a create view (or procedure) statement and the slave doesn't have the user who created it. In mysqldump you will have this format:

CREATE ALGORITHM=UNDEFINED DEFINER=`lipl_ga_app`@`%` SQL SECURITY DEFINER...

That's not specific to replication. It happens even if you would want to restore it to an empty db.

On the replication side

1) One way to do it is mysqldump but you need to LOCK the tables or make sure you don't get any writes during the process of dump (read_only=1 for example). Otherwise you get your slave out of sync from the start. Also there is certainly an impact on the application which is connected to the master. More on this you can read on the mysql doc: http://dev.mysql.com/doc/refman/5.7/en/replication-howto.html

To same or higher major version (these cannot be done if master is 5.6 and slave is 5.5):

2) You can also retrieve the data without impacting the master with hot backup solutions like percona Xtrabackup (https://www.percona.com/doc/percona-xtrabackup/2.3/index.html). This is probably going to be faster as well. Once you applied the logs it behaves as normal mysql binaries and you can do the change master command. You will have an xtrabackup_binlog_info file which contains the master position you need to point the slave to.

3) You can stop the master after acquired the master position, copy the binary to the slave, start mysql and change master to ... with the position you saved. This could be also done by snapshotting the filesystem (LVM, ZFS, BTRFS, etc.) if you want to quickly restart mysql. Usually this is the fastest way.

If your version mismatch you might need to run mysql_upgrade since with the latter two you're copying binary data not logical.

A sidenote:

Why are you replicating from 5.6 to 5.5? Replicating from higher version to lower is not guaranteed to work.

Related Question