实验1 配置MySQL主从同步
实验1 配置MySQL主从同步
系统版本： windows7x64
虚拟机： centos 6.5x64
ip规划： 主192.168.0.29 从192.168.0.42
ssh连接工具： ssh secure shell client
vpn工具： ssl vpn-plus client
实验内容： 1. 为虚拟机安装MySQL数据库
2. 配置MySQL数据库，使本机的mysql客户端可以连接主服务器的MySQL数据库。
3. 为两个虚拟机上的MySQL数据库做主从配置
4. 测试
实验步骤：
第一步 虚拟机安装MySQL数据库
1. 打开vpn连接器，打开ssh连接器，连接主服务器192.168.0.24
2. 下载安装MySQL：
[root@localhost ~]# yum install mysql mysql-server
3. 设置MySQL 数据 root 账户的密码
|
[root@localhost ~]# mysql_secure_installation Enter current password for root 按回车 Set root password? [Y/n] 输入y New password: 输入1 Remove anonymous users? [Y/n] 输入y Disallow root login remotely? [Y/n] 输入n Remove test database and access to it? [Y/n] 输入n Reload privilege tables now? [Y/n] 输入y |
第二步 远程连接MySQL
1. 重启MySQL：/etc/init.d/mysqld restart
2. 授权远程连接：
|
[root@localhost ~]#mysql –uroot –p1
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '1' WITH GRANT OPTION
mysql> FLUSH PRIVILEGES |
3. 查看授权效果# mysql>use mysql;
|
mysql> select Host,User,Password from user; +-----------------------+------+-------------------------------------------+ | Host | User | Password | +-----------------------+------+-------------------------------------------+ | localhost | root | *E6CC90B878B948C35E92B003C792C46C58C4AF40 | | localhost.localdomain | root | *E6CC90B878B948C35E92B003C792C46C58C4AF40 | | 127.0.0.1 | root | *E6CC90B878B948C35E92B003C792C46C58C4AF40 | | % | root | *E6CC90B878B948C35E92B003C792C46C58C4AF40 | +-----------------------+------+-------------------------------------------+ 4 rows in set (0.00 sec)
mysql> exit Bye |
4. 修改centos防火墙策略，打开3306端口。
(修改文件的方法是键入’a’,即可上下移动光标到需要修改的位置，修改完了之后按’Esc’:q！忽略修改，’Esc’:wq保存修改)
|
[root@localhost ~]# service iptables stop iptables: Setting chains to policy ACCEPT: filter [ OK ] iptables: Flushing firewall rules: [ OK ] iptables: Unloading modules: [ OK ] [root@localhost ~]# vi /etc/sysconfig/iptables # Firewall configuration written by system-config-firewall # Manual customization of this file is not recommended. *filter :INPUT ACCEPT [0:0] :FORWARD ACCEPT [0:0] :OUTPUT ACCEPT [0:0] -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT -A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT -A INPUT -p icmp -j ACCEPT -A INPUT -i lo -j ACCEPT -A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT -A INPUT -j REJECT --reject-with icmp-host-prohibited -A FORWARD -j REJECT --reject-with icmp-host-prohibited COMMIT ~ ~ ~ ~ ~ ~ ~ "/etc/sysconfig/iptables" 14L, 522C written |
5.从本机登录mysql：打开UME SQL Manager，新建数据库连接。
6. 运行结果
第三步 配置主服务器
1. 修改配置文件
|
[root@localhost ~]# vi /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql log-bin=mysql-bin server-id=111 binlog-do-db=test 同步test数据库的内容 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0
[mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid ~ ~ ~ ~ ~ ~ "/etc/my.cnf" 12L, 283C written |
2. 重启MySQL
3. 主服务器上授权一个同步用的id
|
mysql> GRANT REPLICATION SLAVE ON *.* to 'mysync'@'%' identified by '1'; Query OK, 0 rows affected (0.00 sec) |
4. 查看配置效果
|
mysql> use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
Database changed mysql> select Host,User,Password from user; +-----------------------+--------+-------------------------------------------+ | Host | User | Password | +-----------------------+--------+-------------------------------------------+ | localhost | root | *E6CC90B878B948C35E92B003C792C46C58C4AF40 | | localhost.localdomain | root | *E6CC90B878B948C35E92B003C792C46C58C4AF40 | | 127.0.0.1 | root | *E6CC90B878B948C35E92B003C792C46C58C4AF40 | | % | root | *E6CC90B878B948C35E92B003C792C46C58C4AF40 | | % | mysync | *E6CC90B878B948C35E92B003C792C46C58C4AF40 | +-----------------------+--------+-------------------------------------------+ 5 rows in set (0.00 sec)
mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 245 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) 目前日志偏移量为245 |
第四步 配置从服务器
1. 修改配置文件
|
[root@localhost ~]# vi /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql log-bin=mysql-bin 开启二进制日志 server-id=116 为mysql指定一个id master-host=192.168.0.24 主服务器ip master-user=mysync 同步账号 master-password=1 同步密码 master-port=3306 主服务器的端口 replicate-do-db=test 要同步的数据库 expire_logs_days=10 过期时间 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0
[mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid ~ ~ ~ ~ ~ "/etc/my.cnf" 18L, 283C written |
2. 重启MySQL/etc/init.d/mysqld restart
3. 修改与主服务器连接和通讯的参数
|
mysql> change master to master_host='192.168.0.24',master_user='mysync',master_password='1',master_log_file='mysql-bin.000001',master_log_pos=245; Query OK, 0 rows affected (0.00 sec) |
4. 开启同步
|
mysql> slave start; Query OK, 0 rows affected (0.00 sec)
|
5. 查看slave状态
|
mysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.24 Master_User: mysync Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 我也奇怪这个2号日志文件是怎么出现的 Read_Master_Log_Pos: 258 Relay_Log_File: mysqld-relay-bin.000003 Relay_Log_Pos: 403 Relay_Master_Log_File: mysql-bin.000002 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: 258 Relay_Log_Space: 933 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)
ERROR: No query specified |
第五步 测试主从
1. 通过win7 EMS mysql Manager客户端为test数据库建立goods表
2. 打开从服务器ssh，查看test数据库
|
mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | goods | +----------------+ 1 row in set (0.00 sec) |
3. 以上结果表明192.168.0.24和192.168.0.32之间已经实现主从同步了。
事实上主从同步是将主从都远程连接到客户端，所有操作都直接在客户端上完成。
实验中遇到的问题：
1.配置主从前，在主服务器中输入了数据，再配置主从，所以在配置的时候从服务器里没有那些数据，报错。-------在从服务器的配置文件vi /etc/my.cnf 中忽略该报错。
2.配置主从时，误删了从服务器的文件，报错
3.配置主从时，误删了从服务器数据库（比如在客户端中断开从服务器的连接时，误删了数据库）
不管怎样，就是先将主从服务器中多余的文件删除（或者将所有的删除），然后在主从同步时，把遇到的报错在从服务器配置文件vi /etc/my.cnf 中忽略。
在客户端远程连接时需要注意以下几点：
1.尽量不要在主从配置完成之前像数据库中添加数据，以免造成主从配置的失败（如：主从配置完成后，从服务器中此时没有主服务器里的文件，就会报错）
2.在主服务器中配置的时候，从服务器里没有显示出任何数据，这个时候请关闭从数据库对从服务器的连接，再重新连接从服务器。（这种情况一般发生在主服务器增加了table的时候，如果只是在table中做修改，不需要这样。）
关于客户端的使用：
添加table后要点compile
在table中添加数据后要点refresh
(这两者都在General目录下，位于左侧数据库和右侧具体信息之间)
|
mysql> create table table1(s_no integer,s_name char(50),s_sex char(50),s_major char(50)); Query OK, 0 rows affected (0.01 sec) mysql> drop table table1; Query OK, 0 rows affected (0.00 sec) mysql> create table table1(s_no integer,s_name char(50),s_sex char(50),s_major char(50)); Query OK, 0 rows affected (0.00 sec) mysql> insert into table1 values(1,'Rose','female','Electronic Commerce'); Query OK, 1 row affected (0.00 sec) mysql> select * from table1; +------+--------+--------+---------------------+ | s_no | s_name | s_sex | s_major | +------+--------+--------+---------------------+ | 1 | Rose | female | Electronic Commerce | +------+--------+--------+---------------------+ 1 row in set (0.00 sec)
mysql> |
