MySql Master
MySql Master my.cnf
$ cat /etc/my.cnf [mysqld] datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock user=mysql server-id=1 default-storage-engine = InnoDB innodb_file_per_table = 1 log-bin=mysql-bin innodb_flush_log_at_trx_commit=1 sync_binlog=1
MySql Master Show status
mysql> show status; +-----------------------------------+----------+ | Variable_name | Value | +-----------------------------------+----------+ | Aborted_clients | 0 | | Aborted_connects | 4 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 682 | | Slave_open_temp_tables | 0 | | Slave_retried_transactions | 0 | | Slave_running | OFF | | Threads_cached | 0 | | Threads_connected | 1 | | Threads_created | 275 | | Threads_running | 1 | | Uptime | 65819 | | Uptime_since_flush_status | 4722 | +-----------------------------------+----------+ 249 rows in set (0.00 sec)
MySql Master Show Master Status
mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 | 459433 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
MySql Master Create a Snapshot
lock the tables in one session and mysqldump in anothermysql> flush tables with read lock; Query OK, 0 rows affected (0.00 sec)
mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 | 461451 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
$ mysqldump -p mydb --master-data > mydb.sql
check the binary file name and binary file position match
-- -- Position to start replication or point-in-time recovery from -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=461451;
mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)
MySql Master Show Master Status
mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 | 463398 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
MySql Master Files
$ ls -l /var/lib/mysql/ total 29352 -rw-rw---- 1 mysql mysql 18874368 Oct 5 11:10 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Oct 5 11:10 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Oct 1 10:15 ib_logfile1 drwx------ 2 mysql mysql 4096 Sep 6 14:32 mysql -rw-rw---- 1 mysql mysql 80153 Oct 4 16:45 mysql-bin.000001 -rw-rw---- 1 mysql mysql 461380 Oct 5 11:10 mysql-bin.000002 -rw-rw---- 1 mysql mysql 38 Oct 4 16:48 mysql-bin.index srwxrwxrwx 1 mysql mysql 0 Oct 4 16:48 mysql.sock
MySql Master Processes
mysql> show processlist; | Id | User | Host | db | Command | Time | State | Info | | 314 | replica | 192.168.100.67:43508 | NULL | Binlog Dump | 443 | Has sent all binlog to slave; waiting for binlog to be updated 2 rows in set (0.00 sec)
REFERRERS
MySqlReplication