技术改变世界 阅读塑造人生! - shaogx.com

This string was altered by TechBlog\Plugins\Example.; This is an example to show the potential of an offcanvas layout pattern in Bootstrap. Try some responsive-range viewport sizes to see it in action.

mysql互为主备

有server1=192.168.0.17 和 server2=192.168.0.18俩台mysql server其中;server1主机上配置server-id=1      server2上配置:server-id=2 17 Server1上mysql的配置:linux 内核:#uname -r2.6.32-220.el6.x86_64 mysql版本:#mysql -u root -p Enter password:输入mysql密码mysql> status-------------- ...............................mysql  Ver 14.14 Distrib 5.5.22, for Linux (x86_64) using  EditLine wrapper  //数据版本号 ................................--------------//创建mysql同步用户,只允许bak这个用户通过192.168.0.18这台主机上连接。mysql>grant replication slave on *.* to bak@192.168.0.18 identified by 'wdmysql'; 编辑mysq1主配置文件,在/etc/my.cnf中添加以下内容 server-id = 1   #最好是找到这一行,省的手工添加避免冲突, character_set_server=utf8log-bin = mysql-bin binlog-ignore-db = mysql binlog-do-db = bbsdb binlog-do-db = phpmyadmin replicate-do-db = phpmyadmin binlog-ignore-db = test # master-host = 192.168.0.18 #master-user = bak #master-password = wdmysql #master-port = 3306  #master-connect-retry = 60 replicate-ignore-db = mysql replicate-ignore-db = test replicate-do-db = bbsdb //重新启动mysql server#server mysql restart 18  Server2上mysql的配置:linux 内核:#uname -r2.6.32-220.el6.x86_64 mysql版本:#mysql -u root -p Enter password:输入mysql密码mysql> status-------------- ...............................mysql  Ver 14.14 Distrib 5.5.22, for Linux (x86_64) using  EditLine wrapper  //数据版本号 ................................--------------创建mysql同步用户,只允许bak这个用户通过192.168.0.17这台主机上连接。grant replication slave on *.* to bak@192.168.0.17 identified by 'wdmysql'; 编辑mysq2主配置文件,在/etc/my.cnf中添加以下内容 [mysqld]       #找到这一行,在这个下面添加以下内容;server-id = 2                               #//最好是找到这一行,省的手工添加冲突, character_set_server=utf8          #这个对本次试验没有时间用处,在网上看见有人改mysql字符集报错,随便我在这给加上了 #master-host = 192.168.0.17         # 指定主服务器IP地址 #master-user = bak                        # 指定在主服务器上可以进行同步的用户名 #master-password = wdmysql            # 密码 #master-port = 3306                         # 同步所用端口 #master-connect-retry = 60               # 断点从新连接时间 replicate-ignore-db = mysql             # 屏蔽对mysql库的同步 replicate-ignore-db = test            # 屏蔽对test库的同步 binlog-ignore-db =mysqlreplicate-do-db = bbsdb                      # 同步的数据库的名称 binlog-do-db = bbsdb                      # 二进制需要同步的数据库名 replicate-do-db = phpmyadmin                     # 同步的数据库的名称 binlog-do-db = phpmyadmin                      # 二进制需要同步的数据库名 binlog-ignore-db = test  //上面带有 "master_" 都被我注释掉了,因为我的mysql不支持这些参数; 然后重新启动mysql服务,#server mysql restart //下面的步骤都需要在俩台mysql上操作一遍; #mysql -u root -p Enter password:输入mysql密码..................mysql> stop slave;mysql> start slave;mysql> show slave status\G //主要看这两行,是否都为“yes”,如果不是,请查看日志文件,一般都有提示;................................. Slave_IO_Running: Yes  Slave_SQL_Running: Yes.................................... //一般情况Slave_IO_Running:会出现NO,Slave_SQL_Running一般没事,如果出现以下情况请执行以下操作; 先登录mysql。执行以下命令。 mysql> show slave status\G *************************** 1. row ***************************                Slave_IO_State:                   Master_Host: 192.168.0.18                   Master_User: bak                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000003           Read_Master_Log_Pos: 107                Relay_Log_File: server7-relay-bin.000002                 Relay_Log_Pos: 253         Relay_Master_Log_File: mysql-bin.000003              Slave_IO_Running: No            Slave_SQL_Running: Yes               Replicate_Do_DB: phpmyadmin,bbsdb           Replicate_Ignore_DB: mysql,test            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: 107               Relay_Log_Space: 411               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: NULL Master_SSL_Verify_Server_Cert: No                 Last_IO_Errno: 0                 Last_IO_Error:                Last_SQL_Errno: 0                Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 2 1 row in set (0.00 sec)  从上面的状态可以看到,此时从主库无法同步;然后登录server1主数据库,允许从数据库同步相关数据; mysql> grant replication slave on *.* to bak@192.168.0.18 identified by 'wdmysql'; 再次重启从数据库,登录进去查看状态时,如果还是不行的话再继续往下操作。 mysql> stop slave;//先到另一台mysql上查看 mysql> show master status;+------------------+----------+------------------+------------------+ | File             | Position | Binlog_Do_DB     | Binlog_Ignore_DB | +------------------+----------+------------------+------------------+ | mysql-bin.000003 |      107 | bbsdb,phpmyadmin | mysql,test       | +------------------+----------+------------------+------------------+ 1 row in set (0.00 sec) //在server1上操作mysql> change master to master_host='192.168.0.18',master_user='bak',master_password='wdmysql',master_log_file='mysql-bin.000003',master_log_pos=107; mysql> start slave; mysql> show slave status\G *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.0.18                   Master_User: bak                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000003           Read_Master_Log_Pos: 107                Relay_Log_File: server7-relay-bin.000003                 Relay_Log_Pos: 253         Relay_Master_Log_File: mysql-bin.000003              Slave_IO_Running: Yes             Slave_SQL_Running: Yes              Replicate_Do_DB: phpmyadmin,bbsdb           Replicate_Ignore_DB: mysql,test            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: 107               Relay_Log_Space: 557               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:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 2 1 row in set (0.00 sec) 然后在server2上面再操作一遍,这样就可以实现mysql的实时同步了。可以在bbs数据库创建表,或者创建phpmyadmin数据库上创建表。然后到另一台mysql上查看是否多了一个新表。如果有实验成功~本文出自 “Linux系统架构” 博客,请务必保留此出处http://research.blog.51cto.com/2258479/828926... 全文

休闲 mysql复制 mysql集群 mysql热备份 mysql互为主备

1