MySQL 5.5 复制搭建的示例分析

网友投稿 326 2023-12-25

MySQL 5.5 复制搭建的示例分析

这篇文章主要为大家展示了“MySQL 5.5 复制搭建的示例分析”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“MySQL 5.5 复制搭建的示例分析”这篇文章吧。

MySQL 5.5 复制搭建的示例分析

--Master 192.168.78.139--Slave 192.168.78.137

--在Slave安装好MySQL软件，安装流程可以参考源码安装文章

http://blog.itpub-/26506993/viewspace-2072859/--Master关闭数据库，并拷贝数据文件到Slave[root@localhost backup]# /software/bin/mysqladmin -usystem -pMysql#2015 shutdown[root@localhost backup]# 160426 19:50:32 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended[1]+  Done                    /software/bin/mysqld_safe --defaults-file=/etc/my-f  (wd: /data) (wd now: /backup)[root@localhost backup]# ps -ef|grep 3306 root     20319 55613  0 19:50 pts/2    00:00:00 grep 3306[root@localhost /]# zip -r /install/mysql_data_20160427.zip /data/[root@localhost install]# scp /install/mysql_data_20160427.zip root@192.168.78.137:/install/The authenticity of host 192.168.78.137 (192.168.78.137) cant be established. RSA key fingerprint is 4a:41:41:4b:4b:83:ea:cc:4b:56:bb:20:0a:8c:88:ce.Are you sure you want to continue connecting (yes/no)? yesWarning: Permanently added 192.168.78.137 (RSA) to the list of known hosts. root@192.168.78.137s password: mysql_data_20160427.zip                                                                                                    100% 5053KB   4.9MB/s   00:01--Slave，将传输过来的数据文件解压到Slave所要使用的数据文件目录 [root@localhost install]# mkdir /mysql_data[root@localhost install]# unzip -d /mysql_data/ /install/mysql_data_20160427.zip[root@localhost install]# chown -R mysql.mysql /mysql_data/--编辑Master的配置文件 [root@localhost install]# vim /etc/my-f # Log server-id = 100log-bin = /log/binlog/mysql-bin--启动Master的Mysql数据库[root@localhost backup]# /software/bin/mysqld_safe --defaults-file=/etc/my-f & [1] 20592[root@localhost backup]# 160426 20:32:49 mysqld_safe Logging to /log/err.log. 160426 20:32:49 mysqld_safe Starting mysqld daemon with databases from /data--在Master数据库上面创建复制专用账户 mysql> grant replication slave on *.* to repl@192.168.78.% identified by Mysql#2015;Query OK, 0 rows affected (0.04 sec)--查看Master当前的日志名称和位置，用于下面在Slave的change master to命令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.000008 |      256 |              |                  | +------------------+----------+--------------+------------------+1 row in set (0.00 sec)--配置Slave的配置文件 [root@localhost data]# vim /etc/my-f # Log server-id = 200log-bin = /log/binlog/mysql-bin relay-log = /log/binlog/mysqld-relay-binrelay-log-index = /log/binlog/product-mysql-relay-index datadir = /mysql_data/data--启动Slave的数据库服务[root@localhost mysql]# /data/bin/mysqld_safe --defaults-file=/etc/my-f & [1] 22611[root@localhost mysql]# 160426 22:53:18 mysqld_safe Logging to /log/err.log.160426 22:53:18 mysqld_safe Starting mysqld daemon with databases from /mysql_data/data--在Slave配置Slave到Master的连接通过CHANGE MASTER TO语句来设置Slave连接到Master服务器的参数，来使Slave读取Master的二进制日志和Slave的relay日志。 mysql> change master to     -> master_host=192.168.78.139,     -> master_port=3306,-> master_user=repl,     -> master_password=Mysql#2015,     -> master_log_file=mysql-bin.000008,     -> master_log_pos=256;Query OK, 0 rows affected (0.16 sec) 参数含义： master_host 指定连接的Master主机 master_port 指定连接的Master的端口master_user 指定连接的Master的复制专用账户 master_password 指定连接的Master的复制专用账户的密码 master_log_file Master当前的日志名称master_log_pos Master当前的日志位置--Master释放全局只读锁 mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)--查看Slave的状态mysql> show slave status\G *************************** 1. row ***************************                Slave_IO_State: Master_Host: 192.168.78.139                   Master_User: repl                   Master_Port: 3306                 Connect_Retry: 60Master_Log_File: mysql-bin.000008           Read_Master_Log_Pos: 256                Relay_Log_File: mysqld-relay-bin.000001                 Relay_Log_Pos: 4Relay_Master_Log_File: mysql-bin.000008              Slave_IO_Running: No             Slave_SQL_Running: No               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: 256               Relay_Log_Space: 107               Until_Condition: NoneUntil_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: NoLast_IO_Errno: 0                 Last_IO_Error:                 Last_SQL_Errno: 0                Last_SQL_Error:    Replicate_Ignore_Server_Ids: Master_Server_Id: 0 1 row in set (0.00 sec)--启用Slave的应用日志服务START SLAVE语句会启动两个线程。I/O线程会从Master服务器读取事件并将它们存放到relay log中。SQL线程会从relay log中读取事件并执行它们。执行START SLAVE需要SUPER权限。 mysql> start slave;Query OK, 0 rows affected (0.00 sec)--Slave日志中的内容[root@localhost data]# tailf /log/err.log 160427  5:57:08 [Note] CHANGE MASTER TO executed. Previous state master_host=, master_port=3306, master_log_file=, master_log_pos=4. New state master_host=192.168.78.139,master_port=3306, master_log_file=mysql-bin.000010, master_log_pos=107.160427  5:57:23 [Note] Slave SQL thread initialized, starting replication in log mysql-bin.000010 at position 107, relay log /log/binlog/mysqld-relay-bin.000001 position: 4160427  5:57:25 [Note] Slave I/O thread: connected to master repl@192.168.78.139:3306,replication started in log mysql-bin.000010 at position 107

以上是“MySQL 5.5 复制搭建的示例分析”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注行业资讯频道！

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。

标签：示例 配置 参数 数据
上一篇:vue显示代码800A03F2？
下一篇:html引入vue组件？
相关文章

 发表评论

暂时没有评论，来抢沙发吧~