MYSQL PROD to DR replicaiton(master - slave) setup for high avaliablity

Below is the step for DR replication setup for mysql.

Step1:Edit mysql config file
vi /etc/my.cnf
    [mysqld]
    server-id = 1
    log-bin=/var/lib/mysql/master-bin
    expire_logs_days = 10

Step2:Restart mysql    
# /etc/init.d/mysqld restart

Step3: Setup replication in PROD
mysql  -uroot -p******
    show variables like 'log_bin';
     CREATE USER 'repl_slave'@'%' IDENTIFIED BY '*****';
     GRANT REPLICATION SLAVE ON *.* TO 'repl_slave'@'%';
     FLUSH PRIVILEGES;
    SHOW MASTER STATUS;
     FLUSH TABLES WITH READ LOCK;

Step4: Backup:
#mysqldump -uroot -p****** --ignore-table=mysql.event --all-databases --master-data >mysql_dbs_oevent.dump

Step5:copy the dump to DR
scp mysql_dbs_oevent.dump DRIP@/hom

Step6: Edit the DR /etc/my.cfg
    server-id = 2
    read_only
    relay-log=/relay-bin
    expire_logs_days = 10

Step7: Restart mysql    
# /etc/init.d/mysqld restart

Step8: Setup replication in DR

CHANGE MASTER TO MASTER_HOST = 'PRODREPLICATIONIP/Hostname'
   , MASTER_USER = 'repl_slave'
   , MASTER_PASSWORD = '******'
   , MASTER_PORT = 3306;
  
  
Step8: Import the data
mysql -uroot -p******** < mysql_dbs.dump

Step9: Check status in DR
mysql  -uroot -p******
show slave status\G

At DR to stop and start replication
show slave status\G
stop slave;   
start slave;   
show slave status\G;

MYSQL: Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs

In mysql replication using VM clone,getting below error in the DR status

Error:
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

Solution:
in DR: Move /var/lib/mysql/auto.cnf
mv /var/lib/mysql/auto.cnf /var/lib/mysql/auto.cnf_old
and restart mysql

/etc/init.d/mysqld restart

MYSQL : ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

Error:
During reset the password getting below error
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

Solution:
SET GLOBAL validate_password_policy=LOW;
But make sure in prod we have proper policy before changing to LOW or revert once you reset the password.

ALTER USER 'root'@'localhost' IDENTIFIED BY '********';
FLUSH PRIVILEGES;

MYSQL ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

Error:

On select statement

mysql> SELECT user, host FROM mysql.user;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

Solution:

ALTER USER 'root'@'localhost' IDENTIFIED BY '******';
FLUSH PRIVILEGES;

MYSQL: [Warning] Using a password on the command line interface can be insecure.

Error:

[root@PROD01 ~]# mysql -uroot -p*******< /root/mysql_dbs_oevent.dump
mysql: [Warning] Using a password on the command line interface can be insecure.
Please use --connect-expired-password option or invoke mysql in interactive mode.
[root@PROD01 ~]#

Solution:

ALTER USER 'root'@'localhost' IDENTIFIED BY '*******';
FLUSH PRIVILEGES;

MYSQL:mysqldump: Error: Binlogging on server not active

Error:

During mysql backup getting below error:

mysqldump: Error: Binlogging on server not active

Solution:

add below /etc/my.cnf
log-bin=mysql-bin

MYSQL : [Warning] '--log' is deprecated and will be removed in a future release. Please use ''--general_log'/'--general_log_file'' instead.

Error message:

[Warning] '--log' is deprecated and will be removed in a future release. Please use ''--general_log'/'--general_log_file'' instead.
/usr/libexec/mysqld: File '/mysqldata/var/log/mysql/mysql-bin.index' not found (Errcode: 13)

Solution:


Modify /etc/my.cnf
from log-bin=/mysqldata/var/log/mysql/mysql-bin.log
to log-bin=mysql-bin