Mysql 复制的各种操作

hnynes

最近对mysql的复制用得比较多，防止忘掉，在此记下各种操作。


You do not need the line
master-port=
of the slave, if you not changed the port. Normal :
3306
To add a user for the master server you need this
line
mysql> GRANT FILE ON *.* TO repl@"%"
IDENTIFIED BY '';
You have to change "%" to the IP Adress from the
SLAVE.
Example: mysql> GRANT FILE ON *.* TO
repl@192.168.0.2 IDENTIFIED BY 'yourpass';

A few things I came across while setting up
replication:
- Passwords can only be 16 characters long. This will
cause 'Access Denied' errors while trying to connect
to the master if set too long.
- When running replication numerous files are
created that can cause problems getting back on
track if something goes wrong. If there are
problems after you edit your my.cnf and restart
mysqld here's some cleaning up that needs to be
done while the server is shutdown (your file names
might differ):
1) On the slave (in the mysql data dir): remove
master.info file, remove all binary files created and
their indexes, remove the .err and .pid files, remove
the log.info file.
2) On the master (in the mysql data dir): remove all
binary files created and their indexes, remove
the .err and .pid files.
3) If for some reason you need to redo replication I
have found it is best to tar up the mnaster and put a
fresh copy of the database on the slave and start
again rather than trying to resolve every issue the
slave spits out. Although, it should be noted that this
is not always possible - it's a judgement call.


Here is what I had to do to set up replication with LOAD DATA FROM MASTER:
1. Edit the my.cnf file on the slave and on the master server:
master my.cnf:
[mysqld]
server-id = 1
log-bin
slave my.cnf:
[mysqld]
server-id = 2
master-host = master-host.net
master-user = repl
master-password = repl_pass
replicate-do-db = database1
replicate-do-db = database2
log-warnings
2. Restart both the slave and the master.
3. On the master do:
GRANT SUPER,REPLICATION CLIENT,REPLICATION SLAVE,RELOAD ON *.* TO repl@"%" IDENTIFIED BY 'repl_pass';
4. On the slave do:
LOAD DATA FROM MASTER;
The slave should now download the databases specified with replicate-do-db in the slaves my.cnf, and be replicating new data.

Tested on MySQL versions 4.0.20
1. Edit the my.cnf file on the slave and on the master server:
-master my.cnf:
[mysqld]
server-id = 1
log-bin
-slave my.cnf:
[mysqld]
server-id = 2
#replicate-do-db = database1 # for Replicating specific databases
2. Restart both the slave and the master.
3. SQL SYNTAX:
--On the master do
mysql> GRANT SUPER,REPLICATION CLIENT,REPLICATION SLAVE,RELOAD ON *.* TO repl@"slave.host" IDENTIFIED BY 'password';
--On the SLAVE do
mysql> CHANGE MASTER to MASTER_HOST='master.host’,MASTER_PORT=3306,
MASTER_USER='repl',MASTER_PASSWORD='password',MASTER_LOG_FILE='',MASTER_LOG_POS=4;
mysql> STOP SLAVE; #--If already started
mysql> START SLAVE;
4. On the slave do:
mysql> LOAD DATA FROM MASTER;
The slave should now download the databases specified with replicate-do-db in the slaves my.cnf, and be replicating new data.
NOTE: Please sure that the MASTER_USER had required privilege .
These are the configuration i had try out:
MySQL Failover Circular Replication
===================================
Assume we have 2 servers: Server1 and Server2.
Server1 Settings
================
1. Put the option file my.cnf to Server1 path /etc with these settings:
[mysqld]
log-bin=mysql-bin
server-id = 1
auto_increment_increment = 10
auto_increment_offset = 1
2. Change mode/permission for my.cnf to _rw_r__r__ else mysql will igonore it.
sudo chmod 644 my.cnf
3. Stop and start mysql.
cd /Library/StartupItems/MySQLCOM
sudo ./MySQLCOM stop
sudo ./MySQLCOM start
4. Configure the server:
# create a user for replication process:
cd /usr/local/mysql/bin
./mysql -p -u root
create user replicant@'%' identified by 'password';
# Grant access rights:
GRANT SELECT, PROCESS, FILE, SUPER, REPLICATION CLIENT, REPLICATION SLAVE, RELOAD ON *.* TO replicant@'%';
Flush Privileges;
# Specify the info for the serve2:
CHANGE MASTER TO
MASTER_HOST='ip_of_server2',
MASTER_USER='replication_user_name_on_server2',
MASTER_PASSWORD='replication_password_on_server2';
# Start the listerner:
Start slave;
# Verify whether the replication is working:
show slave status\G
Server2 Settings
================
1. Put the option file my.cnf on to Server2 path /etc
with these settings:
[mysqld]
log-bin=mysql-bin
server-id = 2
auto_increment_increment = 10
auto_increment_offset = 2
2. Change mode/permission for my.cnf to _rw_r__r__ else mysql will igonore it.
sudo chmod 644 my.cnf
3. Stop and start mysql.
cd /Library/StartupItems/MySQLCOM
sudo ./MySQLCOM stop
sudo ./MySQLCOM start
4. Configure the server:
# create a user for replication process:
cd /usr/local/mysql/bin
./mysql -p -u root
create user replicant@'%' identified by 'password';
# Grant access rights:
GRANT SELECT, PROCESS, FILE, SUPER, REPLICATION CLIENT, REPLICATION SLAVE, RELOAD ON *.* TO replicant@'%';
Flush Privileges;
# Specify the info for the serve1:
CHANGE MASTER TO
MASTER_HOST='ip_of_server1',
MASTER_USER='replication_user_name_on_server1',
MASTER_PASSWORD='replication_password_on_server1';
# Example:
#
# CHANGE MASTER TO MASTER_HOST='125.564.12.1',
# MASTER_USER='replicant', MASTER_PASSWORD='password';
# Load data from Server1:
Load Data from Master;
# Start the listerner:
Start slave;


本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u1/37603/showart_2026696.html