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 another
mysql> 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
Valid XHTML :: Valid CSS: :: Powered by WikkaWiki