Re: [Note] Start binlog_dump to slave_server error in master

[cinto thomas]

Please find the info below..

Master1

mysql> show master status;
+------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.003276 | 723634791 | | |
+------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.16.55.68
Master_User: replication
Master_Port: 2255
Connect_Retry: 60
Master_Log_File: mysql-bin.003189
Read_Master_Log_Pos: 731966
Relay_Log_File: relay-bin.000074
Relay_Log_Pos: 252
Relay_Master_Log_File: mysql-bin.003189
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: 731966
Relay_Log_Space: 547
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)

my.cnf

[client]
port = 3306

[mysqld]
sql_mode = NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
ft_min_word_len=3
character_set_client = utf8
character_set_server = utf8
init_connect = 'SET NAMES utf8'
default-storage-engine=innodb

port = 3306
skip-locking
key_buffer = 2048M
max_allowed_packet = 60M
table_cache = 256
sort_buffer_size = 16M
read_buffer_size = 16M
myisam_sort_buffer_size = 64M
thread_cache = 15
query_cache_size = 128M
query_cache_limit = 8M
query_cache_type = 1
thread_concurrency = 16
skip-log-warnings
log-slow-queries = /var/db/mysql/slow.log
skip-name-resolve
server-id = 3
log-bin = mysql-bin
relay-log = relay-bin



innodb_buffer_pool_size = 7168M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 8M
innodb_log_buffer_size = 1M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_thread_concurrency = 16
innodb_file_per_table

max_connections = 500
max_user_connections = 500
max_connect_errors = 80
wait_timeout = 1800

[mysqldump]
quick
max_allowed_packet = 16M

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
ft_min_word_len=3

key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M





Master 2 (set as read only will be changed if master1 goes down )

mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.16.55.86
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.003276
Read_Master_Log_Pos: 744137819
Relay_Log_File: relay-bin.000275
Relay_Log_Pos: 744137965
Relay_Master_Log_File: mysql-bin.003276
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: 744137819
Relay_Log_Space: 744138158
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
1 row in set (0.00 sec)

mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.003189
Position: 731966
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)


my.cnf

[client]
port = 2255
[mysqld]
sql_mode = NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
ft_min_word_len=3
character_set_client = utf8
character_set_server = utf8
init_connect = 'SET NAMES utf8'
log-error=/var/db/mysql/db-slave.err
default-storage-engine=innodb

port = 2255
socket = /tmp/mysql.sock
key_buffer = 512M
max_allowed_packet = 60M
table_cache = 256
sort_buffer_size = 4M
read_buffer_size = 4M
myisam_sort_buffer_size = 4M
thread_cache = 15
query_cache_size = 64M
query_cache_limit = 4M
query_cache_type = 1
thread_concurrency = 8

log-slow-queries = /var/db/mysql/slow.log
skip-name-resolve
server-id = 4
log-bin = mysql-bin
relay-log = relay-bin
read_only
slave-skip-errors=1062


innodb_buffer_pool_size = 1024M
innodb_additional_mem_pool_size = 10M
innodb_log_file_size = 8M
innodb_log_buffer_size = 1M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_thread_concurrency = 8
innodb_file_per_table

max_connections = 80
max_user_connections = 75
max_connect_errors = 80
wait_timeout = 200

[mysqldump]
quick
max_allowed_packet = 16M

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
ft_min_word_len=3

key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout