Imp things to remember:
#### slave entries:
relay-log=mysqld-relay-bin
report-host=hostname
### ssh tunnel with keys:
back01# mysql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
| mywork_ops_backups |
+--------------------+
4 rows in set (0.00 sec)
backup/dump
mysqldump mywork_ops_backups < /database_backups/myworkopsdb-$(date +%m-%d-%Y).sql
restore
mysql> create database ops_backup;
[root@pgvmdc ~]# mysql ops_backup < /root/pg-myworkopsdb-20141107.sql
mysql> describe backup_job;
+-----------------+-----------------------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------------------------------------------------+------+-----+---------+----------------+
| backup_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| job_start_dt_tm | datetime | NO | | NULL | |
| job_end_dt_tm | datetime | NO | | NULL | |
| pod_name | varchar(64) | NO | | NULL | |
| status | enum('Success','Error','Running','Rerun','md5fail') | NO | | NULL | |
| type | enum('inc','full') | NO | | NULL | |
| dateb | varchar(64) | NO | | NULL | |
| filename | varchar(64) | NO | | NULL | |
| filesize | varchar(64) | YES | | NULL | |
| vault | varchar(64) | NO | | NULL | |
+-----------------+-----------------------------------------------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)
select * from backup_job where status ='Error';
mysql> select count(*) from backup_job;
+----------+
| count(*) |
+----------+
| 75977 |
+----------+
1 row in set (0.01 sec)
edit master /etc/my.cnf and restart
##replication master##
server-id=1
log-bin=mysql-bin
report-host=hostname
##replication master##
auto_increment_increment=2
auto_increment_offset=1
edit slave /etc/my.cnf and restart
##replication slave##
server-id=2
relay-log=mysqld-relay-bin
report-host=hostname
##replication slave##
auto_increment_increment=2
auto_increment_offset=2
replication user on master
mysql> create user vrep identified by 'vrepvrep';
Query OK, 0 rows affected (0.00 sec)
mysql> create user vrep identified by '2014#vrep';
Query OK, 0 rows affected (0.03 sec)
mysql> select host, user, password from mysql.user;
+---------------------+------+-------------------------------------------+
| host | user | password |
+---------------------+------+-------------------------------------------+
| localhost | root | |
| pgvmdc.myworkdev.com | root | |
| 127.0.0.1 | root | |
| localhost | | |
| pgvmdc.myworkdev.com | | |
| % | vrep | *63536F52A6C2FC15C6CBECB7ADB3F9299D86D6B3 |
+---------------------+------+-------------------------------------------+
6 rows in set (0.00 sec)
mysql> grant replication slave on *.* to 'vrep'@'%' identified by 'vrepvrep';
Query OK, 0 rows affected (0.00 sec)
or
mysql> grant replication slave on *.* to vrep identified by 'vrepvrep';
Query OK, 0 rows affected (0.00 sec)
mysql> set password for 'vrep' = password('vrep');
Query OK, 0 rows affected (0.00 sec)
find master co-ordinates
mysql> flush tables with read lock;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 320 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
Setting the Master Configuration on the Slave:
mysql> change master to master_host='mysqlmaster';
Query OK, 0 rows affected (0.02 sec)
mysql> change master to master_user='vrep';
Query OK, 0 rows affected (0.01 sec)
mysql> change master to master_password='vrep';
Query OK, 0 rows affected (0.01 sec)
mysql> change master to master_log_file='vrep.log';
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_log_pos=320;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec
##################################registration error###################################
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: mysqlmaster
Master_User: vrep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: vrep.log
Read_Master_Log_Pos: 1147
Relay_Log_File: mysqld-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: vrep.log
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1147
Relay_Log_Space: 106
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1597
Last_IO_Error: Master command COM_REGISTER_SLAVE failed: Access denied for user 'vrep'@'mysqlslave' (using password: YES) (Errno: 1045)
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
##################################connection error#####################################
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: mysqlmaster
Master_User: vrep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 366
Relay_Log_File: mysqld-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 366
Relay_Log_Space: 106
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1045
Last_IO_Error: error connecting to master 'vrep@mysqlmaster:3306' - retry-time: 60 retries: 86400
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
################################after slave connects:##########################################
mysql>>; show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: mysqlmaster
Master_User: vrep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 988
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 988
Relay_Log_Space: 407
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
################################after slave connects:##########################################
mysql>>; show processlist;
+----+------+------------------+------------+-------------+------+----------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+------------------+------------+-------------+------+----------------------------------------------------------------+------------------+
| 2 | root | localhost | ops_backup | Query | 0 | NULL | show processlist |
| 34 | vrep | mysqlslave:35958 | NULL | Binlog Dump | 282 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
+----+------+------------------+------------+-------------+------+----------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
ssh tunnel,make sure ssh keys are set
ssh -N -f -L 5007:localhost:3306 root2@209.207.232.136
Errors:
Slave I/O: Master command COM_REGISTER_SLAVE failed: failed registering on master, reconnecting to try again
soln:
[ERROR] Slave I/O: error connecting to master, Error code 1045
The MySQL troubleshooting page gives a few hints, talking about credentials for the replication user. However, attempting a connection via the command line client works, so the password is OK and the slave can reach the master. As it turns out, for replication purposes, the maximum password length is 32 characters.
max is 32
min is 8
Errors
Can't execute the query because you have a conflicting read lock
Soln
show processlist;
check
on master
mysql>>; update backup_job set filesize=40000 where backup_id=131710;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>>; update backup_job set filesize=44800 where backup_id=131710;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>>; update backup_job set filesize=44900 where backup_id=131710;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
on slave
mysql>>; select * from backup_job where backup_id='131710';
+-----------+---------------------+---------------------+----------+--------+------+----------+------------------------------------+----------+-------+
| backup_id | job_start_dt_tm | job_end_dt_tm | pod_name | status | type | dateb | filename | filesize | vault |
+-----------+---------------------+---------------------+----------+--------+------+----------+------------------------------------+----------+-------+
| 131710 | 2014-11-07 07:15:15 | 2014-11-07 07:15:17 | drvv12 | Error | inc | 20141107 | inc-vault-3971-201411070715.tar.gz | 40000 | 3971 |
+-----------+---------------------+---------------------+----------+--------+------+----------+------------------------------------+----------+-------+
1 row in set (0.00 sec)
mysql>>; select * from backup_job where backup_id='131710';
+-----------+---------------------+---------------------+----------+--------+------+----------+------------------------------------+----------+-------+
| backup_id | job_start_dt_tm | job_end_dt_tm | pod_name | status | type | dateb | filename | filesize | vault |
+-----------+---------------------+---------------------+----------+--------+------+----------+------------------------------------+----------+-------+
| 131710 | 2014-11-07 07:15:15 | 2014-11-07 07:15:17 | drvv12 | Error | inc | 20141107 | inc-vault-3971-201411070715.tar.gz | 44800 | 3971 |
+-----------+---------------------+---------------------+----------+--------+------+----------+------------------------------------+----------+-------+
1 row in set (0.00 sec)
mysql>>; select * from backup_job where backup_id='131710';
+-----------+---------------------+---------------------+----------+--------+------+----------+------------------------------------+----------+-------+
| backup_id | job_start_dt_tm | job_end_dt_tm | pod_name | status | type | dateb | filename | filesize | vault |
+-----------+---------------------+---------------------+----------+--------+------+----------+------------------------------------+----------+-------+
| 131710 | 2014-11-07 07:15:15 | 2014-11-07 07:15:17 | drvv12 | Error | inc | 20141107 | inc-vault-3971-201411070715.tar.gz | 44900 | 3971 |
+-----------+---------------------+---------------------+----------+--------+------+----------+------------------------------------+----------+-------+
1 row in set (0.00 sec)
