## How to repeat ## Conf and Build used [umshastr@hod03]/export/umesh/server/binaries/mysql-5.5.45: more master.cnf slave.cnf :::::::::::::: master.cnf :::::::::::::: #Master [mysqld] pid-file = /export/umesh/server/binaries/mysql-5.5.45/run/master.pid socket = /export/umesh/server/binaries/mysql-5.5.45/run/master.sock log-error=/export/umesh/server/binaries/mysql-5.5.45/log/master.log slow_query_log_file=/export/umesh/server/binaries/mysql-5.5.45/log/slow.log general_log_file=/export/umesh/server/binaries/mysql-5.5.45/log/general.log port = 15000 server-id = 1 basedir = /export/umesh/server/binaries/mysql-5.5.45 datadir = /export/umesh/server/binaries/mysql-5.5.45/master tmpdir = /tmp log-bin = /export/umesh/server/binaries/mysql-5.5.45/log/master-bin log-bin-index = /export/umesh/server/binaries/mysql-5.5.45/log/master-bin.index binlog_format=STATEMENT innodb_buffer_pool_size = 1G innodb_log_file_size = 256M innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_file_per_table = 1 :::::::::::::: slave.cnf :::::::::::::: #Slave [mysqld] pid-file = /export/umesh/server/binaries/mysql-5.5.45/run/slave.pid socket = /export/umesh/server/binaries/mysql-5.5.45/run/slave.sock log-error=/export/umesh/server/binaries/mysql-5.5.45/log/slave.log port = 15001 server-id = 2 basedir = /export/umesh/server/binaries/mysql-5.5.45 datadir = /export/umesh/server/binaries/mysql-5.5.45/slave tmpdir = /tmp relay-log-index = /export/umesh/server/binaries/mysql-5.5.45/log/slave-relay-bin.index relay-log = /export/umesh/server/binaries/mysql-5.5.45/log/slave-relay-bin binlog_format=STATEMENT log_slow_slave_statements=ON slow_query_log=ON long_query_time=0 innodb_buffer_pool_size = 1G innodb_log_file_size = 256M innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_file_per_table = 1 [umshastr@hod03]/export/umesh/server/binaries/mysql-5.5.45: # commit: 49c3c543e755dc963d277fc9ccbde9d9a805807e date: 2015-05-12 20:27:26 +0530 build-date: 2015-05-17 12:01:01 +0200 short: 49c3c54 branch: mysql-5.5 MySQL source 5.5.45 ## Initialize sys [umshastr@hod03]/export/umesh/server/binaries/mysql-5.5.45: scripts/mysql_install_db --defaults-file=./master.cnf Installing MySQL system tables... 150612 13:51:29 [Note] /export/umesh/server/binaries/mysql-5.5.45/bin/mysqld (mysqld 5.5.45-enterprise-commercial-advanced-log) starting as process 24678 ... OK Filling help tables... 150612 13:51:29 [Note] /export/umesh/server/binaries/mysql-5.5.45/bin/mysqld (mysqld 5.5.45-enterprise-commercial-advanced-log) starting as process 24685 ... OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /export/umesh/server/binaries/mysql-5.5.45/bin/mysqladmin -u root password 'new-password' /export/umesh/server/binaries/mysql-5.5.45/bin/mysqladmin -u root -h hod03 password 'new-password' Alternatively you can run: /export/umesh/server/binaries/mysql-5.5.45/bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd /export/umesh/server/binaries/mysql-5.5.45 ; /export/umesh/server/binaries/mysql-5.5.45/bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd /export/umesh/server/binaries/mysql-5.5.45/mysql-test ; perl mysql-test-run.pl Please report any problems at http://bugs.mysql.com/ [umshastr@hod03]/export/umesh/server/binaries/mysql-5.5.45: scripts/mysql_install_db --defaults-file=./slave.cnf Installing MySQL system tables... 150612 13:51:36 [Warning] /export/umesh/server/binaries/mysql-5.5.45/bin/mysqld: ignoring option '--log-slow-slave-statements' due to invalid value 'ON' 150612 13:51:36 [Note] /export/umesh/server/binaries/mysql-5.5.45/bin/mysqld (mysqld 5.5.45-enterprise-commercial-advanced-log) starting as process 25044 ... OK Filling help tables... 150612 13:51:36 [Warning] /export/umesh/server/binaries/mysql-5.5.45/bin/mysqld: ignoring option '--log-slow-slave-statements' due to invalid value 'ON' 150612 13:51:36 [Note] /export/umesh/server/binaries/mysql-5.5.45/bin/mysqld (mysqld 5.5.45-enterprise-commercial-advanced-log) starting as process 25051 ... OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /export/umesh/server/binaries/mysql-5.5.45/bin/mysqladmin -u root password 'new-password' /export/umesh/server/binaries/mysql-5.5.45/bin/mysqladmin -u root -h hod03 password 'new-password' Alternatively you can run: /export/umesh/server/binaries/mysql-5.5.45/bin/mysql_secure_installation which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers. See the manual for more instructions. You can start the MySQL daemon with: cd /export/umesh/server/binaries/mysql-5.5.45 ; /export/umesh/server/binaries/mysql-5.5.45/bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd /export/umesh/server/binaries/mysql-5.5.45/mysql-test ; perl mysql-test-run.pl Please report any problems at http://bugs.mysql.com/ [umshastr@hod03]/export/umesh/server/binaries/mysql-5.5.45: ## Start Master/Slave [umshastr@hod03]/export/umesh/server/binaries/mysql-5.5.45: bin/mysqld_safe --defaults-file=./master.cnf & [1] 25060 [umshastr@hod03]/export/umesh/server/binaries/mysql-5.5.45: 150612 13:52:35 mysqld_safe Logging to '/export/umesh/server/binaries/mysql-5.5.45/log/master.log'. 150612 13:52:35 mysqld_safe Starting mysqld daemon with databases from /export/umesh/server/binaries/mysql-5.5.45/master [umshastr@hod03]/export/umesh/server/binaries/mysql-5.5.45: bin/mysqld_safe --defaults-file=./slave.cnf & [2] 25363 [umshastr@hod03]/export/umesh/server/binaries/mysql-5.5.45: 150612 13:52:42 mysqld_safe Logging to '/export/umesh/server/binaries/mysql-5.5.45/log/slave.log'. 150612 13:52:42 mysqld_safe Starting mysqld daemon with databases from /export/umesh/server/binaries/mysql-5.5.45/slave ## Setup Master/Slave replication MasterSession1> CREATE USER 'repl'@'localhost' IDENTIFIED BY 'slavepass'; Query OK, 0 rows affected (0.01 sec) MasterSession1> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'localhost'; Query OK, 0 rows affected (0.00 sec) MasterSession1> MasterSession1> CREATE USER 'repl'@'127.0.0.1' IDENTIFIED BY 'slavepass'; Query OK, 0 rows affected (0.00 sec) MasterSession1> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'127.0.0.1'; Query OK, 0 rows affected (0.00 sec) MasterSession1> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) MasterSession1> show master status; +-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-bin.000003 | 666 | | | +-------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) Slave> CHANGE MASTER TO -> MASTER_HOST='localhost', -> MASTER_PORT=15000, -> MASTER_USER='repl', -> MASTER_PASSWORD='slavepass', -> MASTER_LOG_FILE='master-bin.000003', -> MASTER_LOG_POS=666; Query OK, 0 rows affected (0.00 sec) Slave> start slave; Query OK, 0 rows affected (0.00 sec) Slave> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: repl Master_Port: 15000 Connect_Retry: 60 Master_Log_File: master-bin.000003 Read_Master_Log_Pos: 666 Relay_Log_File: slave-relay-bin.000002 Relay_Log_Pos: 254 Relay_Master_Log_File: master-bin.000003 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: 666 Relay_Log_Space: 410 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: 1 1 row in set (0.00 sec) Slave> ## On master, create Schema,load data and alter table (don't wait for slave to sync while altering table) MasterSession1> CREATE USER 'repl'@'localhost' IDENTIFIED BY 'slavepass'; Query OK, 0 rows affected (0.01 sec) MasterSession1> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'localhost'; Query OK, 0 rows affected (0.00 sec) MasterSession1> MasterSession1> CREATE USER 'repl'@'127.0.0.1' IDENTIFIED BY 'slavepass'; Query OK, 0 rows affected (0.00 sec) MasterSession1> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'127.0.0.1'; Query OK, 0 rows affected (0.00 sec) MasterSession1> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) MasterSession1> show master status; +-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-bin.000003 | 666 | | | +-------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) MasterSession1> create database if not exists test; Query OK, 1 row affected, 1 warning (0.00 sec) MasterSession1> use test; Database changed MasterSession1> drop table if exists sbtest1; Query OK, 0 rows affected, 1 warning (0.00 sec) MasterSession1> CREATE TABLE `sbtest1` ( -> `id` int(10) unsigned NOT NULL, -> `k` int(10) unsigned NOT NULL DEFAULT '0', -> `c` char(120) NOT NULL DEFAULT '', -> `pad` char(60) NOT NULL DEFAULT '' -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.00 sec) MasterSession1> set @id:=0; Query OK, 0 rows affected (0.00 sec) MasterSession1> insert into `sbtest1` values -> (@id:=@id+1,@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000)) -> ,(@id:=@id+1,@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000)) -> ,(@id:=@id+1,@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000)) -> ,(@id:=@id+1,@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000)); Query OK, 4 rows affected, 1 warning (0.00 sec) Records: 4 Duplicates: 0 Warnings: 1 MasterSession1> MasterSession1> insert into `sbtest1`(`id`,`k`,`c`,`pad`) select @id:=@id+1,@id:=@id+1,md5(rand()*1000000),md5(rand()*1000000) from `sbtest1` k1, `sbtest1` k2, `sbtest1` k3, `sbtest1` k4,`sbtest1` k5,`sbtest1` k6, `sbtest1` k7, `sbtest1` k8, `sbtest1` k9,`sbtest1` k0,`sbtest1` ka, `sbtest1` kb, `sbtest1` kc, `sbtest1` kd limit 25000000; Query OK, 25000000 rows affected, 2 warnings (4 min 19.66 sec) Records: 25000000 Duplicates: 0 Warnings: 2 MasterSession1> select now();alter table test.sbtest1 add primary key (id); +---------------------+ | now() | +---------------------+ | 2015-06-12 14:03:03 | +---------------------+ 1 row in set (0.00 sec) ERROR 2013 (HY000): Lost connection to MySQL server during query MasterSession1> -- Don't wait for above alter to complete, execute below statement from MasterSession2 MasterSession2> show processlist; select sleep(150);kill 1; select now(); +----+------+-----------------+------+-------------+------+-----------------------------------------------------------------------+-----------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------------+------+-------------+------+-----------------------------------------------------------------------+-----------------------------------------------+ | 1 | root | localhost | test | Query | 12 | manage keys | alter table test.sbtest1 add primary key (id) | | 3 | root | localhost | NULL | Query | 0 | NULL | show processlist | | 4 | repl | localhost:47710 | NULL | Binlog Dump | 431 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | +----+------+-----------------+------+-------------+------+-----------------------------------------------------------------------+-----------------------------------------------+ 3 rows in set (0.00 sec) +------------+ | sleep(150) | +------------+ | 0 | +------------+ 1 row in set (2 min 30.00 sec) Query OK, 0 rows affected (0.00 sec) +---------------------+ | now() | +---------------------+ | 2015-06-12 14:05:45 | +---------------------+ 1 row in set (0.00 sec) MasterSession2> show processlist; +----+------+-----------------+------+-------------+------+-----------------------------------------------------------------------+-----------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------------+------+-------------+------+-----------------------------------------------------------------------+-----------------------------------------------+ | 1 | root | localhost | test | Killed | 182 | manage keys | alter table test.sbtest1 add primary key (id) | | 3 | root | localhost | NULL | Query | 0 | NULL | show processlist | | 4 | repl | localhost:47710 | NULL | Binlog Dump | 601 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | +----+------+-----------------+------+-------------+------+-----------------------------------------------------------------------+-----------------------------------------------+ 3 rows in set (0.00 sec) -- Check table structure from MasterSession1 MasterSession1> show create table sbtest1\G ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 5 Current database: test *************************** 1. row *************************** Table: sbtest1 Create Table: CREATE TABLE `sbtest1` ( `id` int(10) unsigned NOT NULL, `k` int(10) unsigned NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (1 min 30.51 sec) -- Check table structure on Slave Slave> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed Slave> show create table sbtest1\G *************************** 1. row *************************** Table: sbtest1 Create Table: CREATE TABLE `sbtest1` ( `id` int(10) unsigned NOT NULL, `k` int(10) unsigned NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) Slave>