Behnaz: صفحه‌ای جدید حاوی '
Database Replication To manage Database on both server should setup Replication. Debian * '''On Master Server:''' '...' ایجاد کرد

2012-11-27T13:31:00Z

صفحه‌ای جدید حاوی '<div dir=ltr lang=en> Database Replication To manage Database on both server should setup Replication. Debian * '''On Master Server:''' '...' ایجاد کرد

صفحهٔ جدید

<div dir=ltr lang=en>
Database Replication

To manage Database on both server should setup Replication.
Debian

* '''On Master Server:'''

'''Edit the conf file /etc/mysql/my.cnf'''
Comment bind-address line:
# bind-address = 127.0.0.1

Uncomment the following and change the server-id if required:

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log

'''Restart MySQL'''

/etc/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..

'''Enter MySQL shell to create a save user account and password.'''

mysql -u root -ppassw0rd

'''Create a slave user account in MySQL Shell'''

CREATE USER 'rep1'@'%' IDENTIFIED BY 'slavepassword';
GRANT REPLICATION SLAVE ON *.* TO 'rep1'@'%' IDENTIFIED BY 'slavepassword';
FLUSH PRIVILEGES;

'''Check the master status in MySQL shell'''

SHOW MASTER STATUS;

You should then be presented with something similar to this:

+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106 | | |
+------------------+----------+--------------+------------------+

'''On Slave Server'''

'''Edit the file /etc/mysql/my.cnf and uncomment and modify the following to suite:'''

server-id = 2

'''Restart MySQL'''

/etc/init.d/mysql restart

'''Enter MYSQL shell to create a connection to the master:'''

mysql -u root -ppassw0rd

'''Create a connection with the master database in MySQL Shell.'''

CHANGE MASTER TO MASTER_HOST='X.X.X.X',
MASTER_USER='rep1',
MASTER_PASSWORD='slavepassword',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=106;

Note: Replace X.X.X.X with Master's IP address, mysql-bin.000001 with Master's log file name and 106 with Master's log file position.

“Query OK, 0 rows affected” should be displayed at the end of the command.

* Issue further commands in MySQL shell to start the slave.

SLAVE START;
SHOW SLAVE STATUS \G

The status should then be displayed and if all went well we should have the following contained within that output.

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

The last thing you should do is to dump main database, and import it into backup database.

Source: http://home.exetel.com.au/widget/blog/tag/debian-mysql-database-replication/

Note: Slave_SQL_Running will show No if any (I think) error occurs in Master. This document may help you:
http://www.ducea.com/2008/02/13/mysql-skip-duplicate-replication-errors/
'''CentOS'''

To enable Replication :

* '''On Master Server'''
'''Edit /etc/my.cnf and change these lines :'''

[mysqld]
bind-address = 0.0.0.0
server-id=1
log-bin=/var/run/mysqld/mysqld-bin

'''Restart mysql server'''

/etc/init.d/mysqld restart

'''open mysql console and run commands'''

mysql->CREATE USER 'slave_user' IDENTIFIED BY 'Hdfk@2@2js@h';"
mysql->GRANT REPLICATION SLAVE ON *.* TO slave_user IDENTIFIED BY 'Hdfk@2@2js@h' ;
mysql> use asterisk ;
Database changed
mysql> SHOW MASTER STATUS ;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| mysqld-bin.000001 | 2335 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

* '''On Slave Server'''
'''Edit /etc/my.cnf and change this lines'''

[mysqld]
master-host=192.168.1.17
master-user=slave_user
master-password=Hdfk@2@2js@h
relay-log=/var/run/mysqld/mysqld-relay-bin
server-id=2

'''Restart mysql server'''

/etc/init.d/mysqld restart

'''Restore database which you want to Replication'''
'''Open mysql console and run these commands :'''

mysql> use asterisk ;
Database changed
mysql> SHOW SLAVE STATUS\G
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.000001' , MASTER_LOG_POS=2335 ;

</div>

DMYSQL Replication - تاریخچهٔ ویرایش‌ها

Behnaz: صفحه‌ای جدید حاوی ' Database Replication To manage Database on both server should setup Replication. Debian * '''On Master Server:''' '...' ایجاد کرد

Behnaz: صفحه‌ای جدید حاوی '
Database Replication To manage Database on both server should setup Replication. Debian * '''On Master Server:''' '...' ایجاد کرد