หน้าแรก / Linux / mysql replicate

 

### ####

## master node
1.  config user name  password for slave  on master  node
GRANT REPLICATION SLAVE on *.*  TO ‘slave_user’@'%’  IDENTIFIED BY ‘your_password’;
FLUSH PRIVILEGES;
2 . enable binary log on master node and specific  database for replicate.
#/etc/my.cnf
 log-bin  = /var/lib/mysql/mysql-bin.log
 binlog-do-db = my_database
 server-id =1
3. restart   mysql  on server node
 service mysqld restart
 ## slave node
4. config slave node
# /etc/my.conf
server-id 2
master-host=”ipaddr_masternode”
master-connect-retry=60
master-user=slave_user      ; “username is grant on master node”
master-password = slave_password
replicate-do-db = my_database
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index
5. restart  slave node.
 service  mysqld  restart
6. Getting the data onto the slave
    6.1  Lock write  database on master node.
           %use  my_databases;
           %FLUSH TABLES  WITH READ  LOCK;
    6.2  Dump  data from Master node.

           mysqldump   -u root -p  my_database  |  gzip >   my_database.sql.gz

    6.3  copy data to  slave node
          scp  my_database.sql.gz  root@master_ip:/home/backup
7.  Import  Data to slave node
       7.1 create database
               % create database ‘mydb’ character set uft8 collate utf8_general_ci;
        7.2  import database
              % gunzip  <  my_database.sql.gz  | mysql -u   root -p  my_database
8. Check mater node
    on master node
    % mysql -u root  -e ‘show master status;’
CODE: 

+———————+———-+——————————-+——————+
| File                | Position | Binlog_Do_DB                  | Binlog_Ignore_DB |
+———————+———-+——————————-+——————+
| mysql-bin.000001    | 21197930 | my_database,my_database       |                  |
+———————+———-+——————————-+——————+

9. restart mysql slave on  slave node.
on slave node.
%mysql -u root -p
mysql>slave stop;

    9.1) show slave status\G
    9.2) show global status like ‘slave_running’
10. unlock tables  on mater node.
   On master node
    %unlock tables;
*** lock write database   export and import data from master to slave. and then  unlock write.
check  replicate  and master node
   show slave  status \G on  slave node
   show master status \G on master node
Exec_Master_Log_Pos:  same value.
*  ก่อนทำการ dump ข้อมูลออกมา  ต้องทำการ lock  การเขียนข้อมูล และ ใช้  command   show master status\G

เพื่อนำ  file และ position  มาแก้ไขใน  slave node.