티스토리 뷰

EN/OpenSUSE 11

OpenSUSE 11 MySQL Replication

ServerWorld 2016.05.10 15:53

OpenSUSE 11 MySQL Replication


Configure MySQL Replication settings. This configuration is general Master-Slave settings.



1. Change settings and create a user for replication on MySQL Matser Host.


www:~ # vi /etc/my.cnf

# line 52: uncomment

log-bin=mysql-bin


# line 60: change to another ID you like

server-id=101


www:~ # /etc/init.d/mysql restart 

www:~ # mysql -u root -p 

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 9

Server version: 5.5.45 SUSE MySQL package


Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


# create user (set any password for 'password' section)

mysql> grant replication slave on *.* to replica@'%' identified by 'password'; 

Query OK, 0 rows affected (0.00 sec)


mysql> flush privileges; 

Query OK, 0 rows affected (0.00 sec)


mysql> exit

Bye





2. Install and start MySQL Server on Slave Host, refer to here.



3. Change settings on MySQL Slave Host.


node01:~ # vi /etc/my.cnf

# line 52: uncomment

log-bin=mysql-bin


# line 60: change to another ID you like (different one from Master Host)

server-id=102


# line 61: add

read_only=1


# define own hostname

report-host=node01.srv.world

node01:~ # /etc/init.d/mysql restart 




4. Get Dump-Data on Master Host.


www:~ # mysql -u root -p 

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 9

Server version: 5.5.45 SUSE MySQL package


Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


# lock all tables

mysql> flush tables with read lock; 

Query OK, 0 rows affected (0.00 sec)


# show status (remember File, Position value)

mysql> show master status; 

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000001 |      608 |              |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)



# remain the window above and open the another window and execute dump

www:~ # mysqldump -u root -p --all-databases --lock-all-tables --events > mysql_dump.sql 

Enter password:


# back to the remained window and unlock

mysql> unlock tables; 

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye


# transfer the dump to Slave Host

www:~ # scp mysql_dump.sql node01.srv.world:/tmp/ 

root@node01.srv.world's password:

mysql_dump.sql 100% 515KB 514.7KB/s 00:00




5. Configure replication settings on Slave Host. It's OK all, make sure the settings work normally to create databases on Master Host.


# import dump from Master Host

node01:~ # mysql -u root -p < /tmp/mysql_dump.sql 

Enter password:

node01:~ # mysql -u root -p 

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 9

Server version: 5.5.45 SUSE MySQL package


Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> change master to 

    -> master_host='10.0.0.31',     # Master Hosts's IP

    -> master_user='replica',     # replication ID

    -> master_password='password',     # replication ID's password

    -> master_log_file='mysql-bin.000001',     # File value confirmed on Master

    -> master_log_pos=608;     # Position value confirmed on Master

Query OK, 0 rows affected (0.58 sec)

# start replication

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

# show status

mysql> show slave status\G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 10.0.0.31

                  Master_User: replica

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 608

               Relay_Log_File: mysqld-relay-bin.000002

                Relay_Log_Pos: 535

        Relay_Master_Log_File: mysql-bin.000001

             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: 608

              Relay_Log_Space: 833

              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: 101

               Master_SSL_Crl:

           Master_SSL_Crlpath:

                   Using_Gtid: No

                  Gtid_IO_Pos:

1 row in set (0.00 sec)

저작자 표시 비영리 변경 금지
신고

'EN > OpenSUSE 11' 카테고리의 다른 글

OpenSUSE 11 FTP Client SUSE  (0) 2016.05.10
OpenSUSE 11 FTP Server Install Vsftpd  (0) 2016.05.10
OpenSUSE 11 MySQL Replication  (0) 2016.05.10
OpenSUSE 11 MySQL Install MySQL  (0) 2016.05.10
OpenSUSE 11 Apache2 WebDAV Settings  (0) 2016.05.08
OpenSUSE 11 Apache2 Basic Authentication  (0) 2016.05.08
댓글