一、安装MySQL
可参考LAMP配置进行安装
二、配置从MySQL
//当搭建好一个跑3306端口的MySQL后，再搭建一个3307端口的MySQL2 [root@localhost ~]# cd /usr/local/ [root@localhost local]# cp -r mysql mysql_2 [root@localhost local]# cd mysql_2 [root@localhost mysql_2]# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql2 [root@localhost mysql_2]# cp /etc/my.cnf ./my.cnf [root@localhost mysql_2]# vim my.cnf ... [mysqld] port = 3306 //改为 3307 socket = /tmp/mysql.sock //改为/tmp/mysql2.sock //再添加一行 datadir = /data/mysql2 ... ... //启动 [root@localhost mysql_2]# cd bin/ [root@localhost bin]# ./mysqld_safe --defaults-file=../my.cnf --user=mysql & [root@localhost bin]# echo "./mysqld_safe --defaults-file=../my.cnf --user=mysql &" >>/etc/rc.d/rc.local //加入自动启动服务 //查看是否启动了2个MySQL [root@localhost ~]# netstat -lnp |grep mysqld tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3169/mysqld tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 3037/mysqld unix 2 [ ACC ] STREAM LISTENING 29027 3037/mysqld /tmp/mysql2.sock unix 2 [ ACC ] STREAM LISTENING 29155 3169/mysqld /tmp/mysql.sock
三、配置replication
// 3307 --主master ，3306 --从slave //实验： 先在master上创建库db1，并把库数据复制给slave [root@localhost bin]# mysql -uroot -S /tmp/mysql2.sock mysql> create database db1; Query OK, 1 row affected (0.01 sec) mysql> quit Bye //数据备份 [root@localhost bin]# mysqldump -uroot -S /tmp/mysql2.sock mysql > 123.sql [root@localhost bin]# mysql -uroot -S /tmp/mysql2.sock db1 < 123.sql
1.设置master
[root@localhost ~]# vim /usr/local/mysql_2/my.cnf ... [mysqld] //查看是否有以下内容，没有则添加 server-id=1
log-bin=mysql-bin //以下参数可选择性使用 binlog-do-db=databasename1,databasename2 //需要复制的库名 binlog-ignore-db=databasename1,databasename2 //不需要复制的库名 [root@localhost ~]# pid=`ps uax |grep mysql2.sock |grep -v grep |awk '{print $2}'` [root@localhost ~]# kill -0 $pid; sleep 3; kill $pid [root@localhost ~]# cd /usr/local/mysql_2/bin/ [root@localhost bin]# ./mysqld_safe --defaults-file=../my.cnf --user=mysql & [root@localhost ~]# mysqladmin -u root -S /tmp/mysql2.sock password '123456' [root@localhost ~]# mysql -u root -S /tmp/mysql2.sock -p'123456' mysql> grant replication slave on *.* to 'repl'@'127.0.0.1' identified by '123123'; mysql> flush tables with read lock; //锁定数据库，此时不允许更改任何数据 mysql> show master status; //查看状态，这些数据是要记录的，一会要在slave端用到 +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000006 | 474952 | | | +------------------+----------+--------------+------------------+
2.设置slave
[root@localhost ~]# vim /etc/my.cnf ... //此id不能和master的一样 server-id = 1 //删除或改为 server-id = 2 //选择性添加 replicate-do-db=databasename1,databasename2 replicate-ignore-db=databasename1,databasename2 [root@localhost ~]# service mysqld restart [root@localhost ~]# mysqldump -uroot -S /tmp/mysql2.sock -p123456 db1 > db1.sql [root@localhost ~]# mysql -uroot -S /tmp/mysql.sock -pyourpassword -e "create database db1" [root@localhost ~]# mysql -uroot -S /tmp/mysql.sock -pyourpassword db1 < db1.sql [root@localhost ~]# mysql -uroot -S /tmp/mysql.sock -pyourpassword mysql> slave stop; mysql> change master to master_host='127.0.0.1', master_port=3307, master_user='repl', master_password='123123', master_log_file='mysql-bin.000006', master_log_pos=474952; mysql> slave start; mysql> quit [root@localhost ~]# mysql -uroot -S /tmp/mysql2.sock -p123456 -e "unlock tables" [root@localhost ~]# mysql -uroot -S /tmp/mysql.sock mysql> show slave statusG; //确认以下两项参数都为YES Slave_IO_Running: Yes Slave_SQL_Running: Yes
3.测试主从
/*第1种测试*/ //在master上执行 [root@localhost ~]# mysql -uroot -S /tmp/mysql2.sock -p123456 -e "use db1;select count(*) from db" +----------+ | count(*) | +----------+ | 2 | +----------+ [root@localhost ~]# mysql -uroot -S /tmp/mysql2.sock -p123456 -e "use db1;truncate table db" [root@localhost ~]# mysql -uroot -S /tmp/mysql2.sock -p123456 -e "use db1;select count(*) from db" //晴空了db1库db表的数据 +----------+ | count(*) | +----------+ | 0 | +----------+ //在slave上执行 //从上的表也被清空了 [root@localhost ~]# mysql -uroot -S /tmp/mysql.sock -pyourpassword -e "use db1; select count(*) from db" +----------+ | count(*) | +----------+ | 0 | +----------+ /*第2种测试*/ //主上db表删除了 [root@localhost ~]# mysql -uroot -S /tmp/mysql2.sock -p123456 -e "use db1; drop table db" //从上查看 ，表也不见了 [root@localhost ~]# mysql -uroot -S /tmp/mysql.sock -pyourpassword -e "use db1; select count(*) from db" ERROR 1146 (42S02) at line 1: Table 'db1.db' doesn't exist
主从配置起来很简单，但是这种机制也是非常脆弱的，一旦我们不小心在从上写了数据，那么主从也就被破坏了。另外如果重启master，务必要先把slave停掉，也就是说需要在slave上去执行 slave stop 命令，然后再去重启master的mysql服务，否则很有可能就会中断了。当然重启完后，还需要把slave给开启 slave start.