2

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,

0

Move server-id=3 from under [mysqld_safe] and put under [mysqld]

[mysqld]
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
server-id=3
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#max_connections
max_connections=400
symbolic-links=0
#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

Then restart mysql

Perhaps [mysqld_safe] does not understand server-id. Normally, options not understood by [mysqld_safe] are supposed to passed to [mysqld]. That's probably broken in MySQL 5.1.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.