mysql主从复制配置与读写分离.doc

Mysql主从复制配置主从复制配置与读写分离与读写分离与读写分离实验前准备实验前准备::在VM中安装2台主机,并且在2台主机上均已经安装过mysql数据库,数据库的安装可使用yum安装或者使用编译。master::、主机数据库中没有数据主机数据库中没有数据::::如果是编译安装建议先进行以下操作:#cp/usr/local/mysql/share/mysql//mysqld#cp/usr/local/mysql/bin/mysql/usr/local/bin/mysql#cp/usr/local/mysql/bin/mysqladmin/usr/local/bin/f#vim/etc/f确认以下参数server-id=1注:master主机的server-id一定要为1启动mysql进行设置,之前建议可以更换mysql的ROOT密码#mysql-uroot-p进入mysql后进行如下设置mysql>GRANTREPLICATIONSLAVEON*.*to'用户名'@'slave主机地址'identifiedby'用户密码';上面此步是授权给SLAVE主机复制,并创建一个用户供SLAVE主机使用且设置密码例如:mysql>GRANTREPLICATIONSLAVEON*.*to'gangdan0083'@''identifiedby'123456';mysql>showmasterstatus;具体如下显示+------------------+----------+--------------+------------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|+------------------+----------+--------------+------------------+|mysql-|106|||+------------------+----------+--------------+------------------+记录下所显示的File名称和Position名称,至此master主机设置完成:主机的配置::#cp/usr/local/mysql/share/mysql//mysqld#cp/usr/local/mysql/bin/mysql/usr/local/bin/mysql#cp/usr/local/mysql/bin/mysqladmin/usr/local/bin/f参数#vim/etc/yserver-id=10将server-id参数修改与MASTER主机不同,建议修改至10以上的数字启动mysql进行设置,之前建议可以更换mysql的ROOT密码#mysql-uroot-p进入mysql后进行如下设置mysql>changemasterto-->master_host='master主机IP地址',-->master_user='master主机授权时所创建的用户名',-->master_password='用户密码'-->master_log_file='File值',-->master_log_pos='Position值';例如:mysql>changemasterto-->master_host='',-->master_user='gangdan0083',-->master_password='123456'-->master_log_file='mysql-',-->master_log_pos=106;启动同步:mysql>startslave;mysql>showslavestatus\G;出现以下显示即为成功:******************************************************Slave_IO_State:WaitingformastertosendeventMaster_Host::gangdan0083Master_Port:3306Connect_Retry:60Master_Log_File:mysql-:106Rela