IP mysql server master = 192.168.1.193
IP myslq slave = 192.168.1.176
Master Side
- create user in mysql server master for replicating
user = replicator
pass = replicate
- setup the user privilage
Edit the /etc/my.conf, added the bold lines
copied to slave server
Edit the /etc/my.cnf and added the Bold lines
Make Database for replicating in this example drupaldb
Import the drupa.sql into database
Restart mysql server
reference
IP myslq slave = 192.168.1.176
Master Side
- create user in mysql server master for replicating
user = replicator
pass = replicate
- setup the user privilage
[root@fazries.com ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.1.52-log Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create user replicator@192.168.1.176 identified by 'replicate'; Query OK, 0 rows affected (0.04 sec) mysql> grant replication slave on *.* to replicator@192.168.1.176 identified by 'replicate'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.03 sec) mysql> quit; Bye
Edit the /etc/my.conf, added the bold lines
[root@fazries.com ~]# vi /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-bin = /var/log/mysql/mysql-bin.log binlog-do-db=drupaldb server-id=1 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
[root@fazries1.com ~]# service mysqld start MySQL Daemon failed to start. Starting mysqld: [FAILED] Make the log file and see the master status
[root@fazries1.com ~]# tail -n 50 /var/log/mysql.log 110104 14:28:35 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql /usr/libexec/mysqld: File '/var/log/mysql/mysql-bin.index' not found (Errcode: 2) 110104 14:28:35 [ERROR] Aborting 110104 14:28:35 [Note] /usr/libexec/mysqld: Shutdown complete 110104 14:28:35 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended [root@fazries1.com ~]# mkdir /var/log/mysql [root@fazries1.com ~]# touch /var/log/mysql/mysql-bin.index [root@fazries1.com ~]# chown -R mysql:mysql /var/log/mysql [root@fazries1.com ~]# service mysqld start Starting mysqld: [ OK ]
[root@fazries.com ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.1.52-log Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 106 | drupaldb | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql> quit; ByeDump Database to mysql slave
copied to slave server
[root@fazries.com ~]# mysqldump -u root -p --opt drupaldb > drupal.sql Enter password: [root@fazries1.com ~]# scp drupal.sql root@192.168.1.176:/root root@192.168.1.176's password: drupal.sql 100% 6970KB 410.0KB/s 00:17 [root@fazries1.com ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.1.52-log Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> unlock tables; Query OK, 0 rows affected (0.00 sec) mysql> quit; ByeSlave Side
Edit the /etc/my.cnf and added the Bold lines
[root@fazries.com ~]# cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 server-id=2 master-host=192.168.1.193 master-user=replicator master-password=replicate master-connect-retry=60 replicate-do-db=drupaldb [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
Make Database for replicating in this example drupaldb
Import the drupa.sql into database
Restart mysql server
[root@fazries.com ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 24 Server version: 5.1.51 Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database drupaldb; Query OK, 1 row affected (0.00 sec) mysql> quit; Bye [root@fazries.com ~]# mysql -u root -p drupaldb < /root/drupal.sql Enter password: [root@fazries.com ~]# service mysqld restart Stopping mysqld: [ OK ] Starting mysqld: [ OK ]Replacing the values
[root@fazries.com ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.1.51 Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> stop slave; Query OK, 0 rows affected (0.00 sec) mysql>change master to master_host='192.168.1.193' ->, master_user='replicator' ->, master_password='replicate' ->, master_log_file='mysql-bin.000001' ->, master_log_pos=106; Query OK, 0 rows affected (0.20 sec) mysql> slave start; Query OK, 0 rows affected (0.00 sec) mysql> show slave status; ---------------+-------------------------------+---------------+---------------+----------------+----------------+ | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | +----------------------------------+---------------+-------------+-------------+---------------+----------- | Waiting for master to send event | 192.168.1.193 | replicator | 3306 | 60 | mysql-bin.000001 | 106 | mysqld-relay-bin.000002 | 251 | mysql-bin.000001 | Yes | Yes | drupaldb | | | | | | 0 | | 0 | 106 | 407 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | +----------------------------------+---------------+-------------+-------------+---------------+------------------ 1 row in set (0.00 sec) mysql> quit; Bye
MASTER_HOST is the IP address or hostname of the master (in this example it is 192.168.1.193). MASTER_USER is the user we granted replication privileges on the master. MASTER_PASSWORD is the password of MASTER_USER on the master. MASTER_LOG_FILE is the file MySQL gave back when you ran SHOW MASTER STATUS; on the master. MASTER_LOG_POS is the position MySQL gave back when you ran SHOW MASTER STATUS; on the master.
reference
http://www.howtoforge.com/mysql_database_replication http://www.kalpin.us/2008/12/19/tutorial-replikasi-mysql/
Hey, I am Brazilian, thanks for you for post!
ReplyDelete:-)