I am new to mysql administration. we have master - slave replication environment.
I added a 2nd slave node to existing env [master-slave] and replication is successful. Whenever I restart the mysqld service on 2nd slave node, replication is not starting automatically on the slave. That's what I am trying to accomplish.
I am executing these queries to re-sync again with master node on 2nd slave
2nd slave mysql> start slave;
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO
2nd slave mysql>mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 0 |
+---------------+-------+
1 row in set (0.00 sec)
2nd slave mysql> SET GLOBAL server_id=3;
Query OK, 0 rows affected (0.00 sec)
** I did specify in /etc/my.cnf file, server-id as 3, but its not taking it after restarting the mysqld service.
2nd slave mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: masterhost
Master_User: mysql
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000012
Read_Master_Log_Pos: 17203734
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 616388
Relay_Master_Log_File: mysql-bin.000012
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: 17203734
Relay_Log_Space: 616669
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: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
** from the above command I take master_log_file and MASTER_log_pos to resync.
mysql> CHANGE MASTER TO MASTER_HOST='masterhost', MASTER_USER='mysql', MASTER_PASSWORD='!adbcd', MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=17203734;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
and it starts replicating again from the master. How can I avoid this manual steps in future and replicate automatically ?
Here is my /etc/my.cnf file.
[root@2nd slave ~]# more /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#max_connections
max_connections=400
symbolic-links=0
server-id=3
#skip-slave-start
log-bin=/data/mysql/mysql-bin
#binlog_format=ROW
max_user_connections = 400
key_buffer = 64M
max_allowed_packet = 32M
sort_buffer_size = 64M
net_buffer_length = 64K
read_buffer_size = 16M
read_rnd_buffer_size = 16M
thread_stack = 192K
thread_cache_size = 8
query_cache_limit = 10M
query_cache_size = 64M
innodb_buffer_pool_size = 1000M
innodb_additional_mem_pool_size = 100M
innodb_log_file_size = 10M
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_thread_concurrency = 16
[root@2nd slave ~]#
Thanks,
