主:mysql-5.1.53.tar.gz 192.168.15.168
从:mysql-5.1.53.tar.gz 192.168.15.169
主数据库安装配置
添加mysql账号
useradd mysql -s /sbin/nologin
安装mysql
tar zxvf mysql-5.1.53.tar.gz
cd mysql-5.1.53
./configure --prefix=/usr/local/mysql --localstatedir=/opt/data --with-extra-charsets=utf8,gb2312,gbk --with-pthread --enable-thread-safe-client
make && make install
cp support-files/my-large.cnf /etc/my.cnf
cp support-files/mysql.server /etc/rc.d/init.d/mysqld
chmod 755 /etc/rc.d/init.d/mysqld
chkconfig --add mysqld
chkconfig mysqld on
cd /usr/local/mysql/
chgrp -R mysql .
mkdir /opt/data
chown -R mysql:mysql /opt/data
生成基本的数据库和表
/usr/local/mysql/bin/mysql_install_db --user=mysql
修改配置文件
vi /etc/my.cnf
不同的地方就是server-id,主服务器配置文件不用修改,从服务器的配置文件server-id=10.其他的内容基本相同.
启动服务
/usr/local/mysql/bin/mysqld_safe --user=mysql&
设置root 密码
/usr/local/mysql/bin/mysqladmin -u root password "123456"
/usr/local/mysql/bin/mysql -uroot –p
从服务器基本相同
设置读锁
mysql> flush tables with read lock;
得到binlog日志文件名和偏移量
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 244 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
备份要同步的数据库
/usr/local/mysql/bin/mysqldump -uroot -p test > /opt/data/test.sql
拷贝/opt/data/test.sql到从服务器上,然后在从服务器上
mysql -u root -p < /opt/data/test.sql
解锁
mysql> unlock tables;
将192.168.15.168设为 192.168.15.169的主服务器
在192.168.15.168新建授权用户
mysql> grant replication slave on *.* to 'replication'@'%' identified by 'replication';
在192.168.15.169上将192.168.15.168设置为自己的主服务器
mysql> change master to
master_host='192.168.15.168',master_user='replication',master_password='replication',master_log_file='
mysql-bin.000003',master_log_pos=244;
启用复制功能
mysql> start slave;
mysql> show slave status/G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.15.168
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 398
Relay_Log_File: mysql2-relay-bin.000002
Relay_Log_Pos: 405
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 398
Relay_Log_Space: 561
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
测试
在主服务器test数据库中创建user表
mysql> use test;
mysql> create table user(id int);
在从服务器中查看user表
mysql> use test;
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user |
+----------------+
1 row in set (0.00 sec)
主从服务器同步维护 手工
在主服务器上执行
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 486 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
在从服务器上执行
mysql> select master_pos_wait('mysql-bin.000003','486');
同步完成后,在主服务器上执行解锁
mysql>unlock tables;
切换主从服务器
当主服务器出现故障时,可将从服务器当主服务器来使用.步骤如下:
1、保证所有从数据库都已经执行了relay log中的全部更新,在从服务器中执行
stop slave io_thread,用show processlist检查,查看状态是否是Has read all relay log,表示更新完成.
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> show processlist/G
*************************** 1. row ***************************
Id: 2
User: root
Host: localhost
db: test
Command: Query
Time: 0
State: NULL
Info: show processlist
*************************** 2. row ***************************
Id: 4
User: system user
Host:
db: NULL
Command: Connect
Time: 591
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
2 rows in set (0.00 sec)
2、在从服务器上执行stop slave,reset master命令,重置成主数据库
mysql>stop slave;
Query OK,0 affected (0.00 sec)
mysql>reset master;
Query OK,0 affected (0.00 sec)
3、删除新的主服务器数据库目录中的master.info和relay-log.info文件,否则下次重启时还会按照从服务器来启动.
root@mysql2 mysql]# cd /opt/data/
master.info mysql2-relay-bin.000001 mysql-bin.index
mysql/ mysql2-relay-bin.000002 relay-log.info
mysql2.err mysql2-relay-bin.index test/
mysql2.pid mysql-bin.000001
相关链接
http://blog.csdn.net/cnbird2008/archive/2011/02/11/6178932.aspx
http://sery.blog.51cto.com/10037/88526
http://database.51cto.com/art/201012/237204.htm
http://os.51cto.com/art/200810/92963.htm
http://dev.mysql.com/doc/refman/5.1/zh/replication.html