mysql同步的配置

1.configure the master
vi /etc/my.cnf add the following:
log-bin=/var/log/mysql/mysql-bin.log
binlog-do-db=database name(要同步的数据库的名字）
server-id=1

service mysqld restart
mysql -u root -ppassword
>grant replication slave on *.* to 'slave_user'@'%' identified by 'password';
>flush privileges;
>use database;
>flush tables with read lock;
>show master status:(#this command will show info as follows 记下该命令所显示的信息，重要）
 File                      Position          binlog-do-db    binlog..
 mysql-bin.008             98                databasename
>exit
mysqldump databasename >databasename.sql
mysql -uroot -ppassword
>unlock tables;
>exit;
scp databasename.sql root@remoteip:/root
 
2.configure the slave
mysql -uroot -ppassword
>create database databasename (#建一个与与主服务器的数据库名字相同的数据库）
>exit
mysql databasename vi /etc/my.cnf add following lines:
server-id=2
master-host=master ip address
master-user=slave_user
master-password=somepassword (填写用grant设置权限时所设置的密码）
master-connet-retry=60
replication-do-db=databasename

service mysqld restart
mysql -uroot -ppassword
>slave stop;
>change master to master-host='master ip address',master-user='slave_user'\
>master-password='somepassword',master-log-file='mysql-bin.008',\
>master-log-pos=98;
>start slave;
>quit

>show slave status\G (此命令用于查看mysql同步是否启动，如启动，则会出现以下信息：）
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes


启动多个mysql
/usr/local/mysql/bin/mysqld_muti start/stop

如查一个系统同时运行多个mysql,进入单个mysql的方法是
mysql -uroot -ppassword -h127.0.0.1 -Pport(mysql运行的端口号）
如果不加-h & -P ,默认进的是3306这个端口所运行的mysql


启动单个mysql
/usr/local/mysql/bin/mysqld_safe --user=mysql
/usr/local/mysql/share/mysql/mysql.server  start
/etc/init.d/mysqld start (or service mysqld start)