1.安装MySQL 1.1下载安装包 wget http://dev.mysql.com/Downloads/MySQL-5.6/MySQL-server-5.6.21-1.rhel5.x86_64.rpm wget http://dev.mysql.com/Downloads/MySQL-5.6/MySQL-devel-5.6.21-1.rhel5.x86_64.rpm wget http://dev.mysql.com/Downloads/MySQL-5.6/MySQL-client-5.6.21-1.rhel5.x86_64.rpm 1.2卸载系统预安装的mysql-libs yum remove mysql-libs 1.3安装依赖包 yum install libaio 1.4安装MySQL rpm -ivh MySQL-server-5.6.21-1.rhel5.x86_64.rpm rpm -ivh MySQL-devel-5.6.21-1.rhel5.x86_64.rpm rpm -ivh MySQL-client-5.6.21-1.rhel5.x86_64.rpm 1.5修改配置文件位置 cp /usr/share/mysql/my-default.cnf /etc/my.cnf 1.6初始化数据库 /usr/bin/mysql_install_db 1.7启动数据库 1.7.1设置开启启动 chkconfig mysql on 1.7.2启动数据库 service mysql restart 1.8更改mysql初始化密码 1.8.1查看数据库初始化密码 cat /root/.mysql_secret 1.8.2更改密码 set password=password('1234'); 1.9主从复制配置(Master:主,Slave:从) 1.9.1准备两台主机(在同一网络中) 主机A:Master 主机B:Slave 1.9.2设置防火墙对3306端口开启 最简单的方式: service iptables stop 1.9.3Master配置 1.9.3.1更改配置文件/etc/my.cnf log-bin=mysql-bin server-id=2 binlog-ignore-db=information_schema binlog-ignore-db=cluster binlog-ignore-db=cluster binlog-ignore-db=mysql binlog-do-db=nxb #要备份的数据库 1.9.3.1重启mysql数据库 service mysql restart 1.9.3.2进入数据库,赋予从库权限,允许从库在主库上读取日志。 1)赋予Slave机器File权限 GRANT FILE ON *.* TO 'root'@'192.168.40.148' IDENTIFIED BY '1234'; 2)赋予Slave机器REPLICATION SLAVE权限 GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.40.148' IDENTIFIED BY '1234'; FLUSH PRIVILEGES; 1.9.3.3重新启动mysql,登录mysql,查看主机信息 show master status; +------------------+----------+--------------+---------------------------------- +-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+---------------------------------- +-------------------+ | mysql-bin.000002 | 560 | test | information_schema,cluster,mysql | | +------------------+----------+--------------+---------------------------------- +-------------------+ 这里的 File 、Position 是在配置Salve的时候要使用到的,Binlog_Do_DB表示要同步的数据库, Binlog_Ignore_DB 表示Ignore的数据库,这些都是在配置的时候进行指定的。 如果执行这个步骤始终为Empty set(0.00 sec),那说明前面的my.cnf没配置对。 1.9.4Slave配置 1.9.4.1修改从库配置文件:/etc/my.cnf log-bin=mysql-bin server-id=3 binlog-ignore-db=information_schema binlog-ignore-db=cluster binlog-ignore-db=mysql replicate-do-db=nxb #要备份的数据库 replicate-ignore-db=mysql log-slave-updates slave-skip-errors=all slave-net-timeout=60 1.9.4.2注意, 不需要配置,配置之后会出错 master-host=192.168.1.1#Master的主机IP master-user=root master-password=mysql password#Master的MySQL密码 不需要配置,配置之-后会出错 1.9.4.3配置文件修改完成后重启mysql,进入Slave mysql控制台,执行: stop slave;#关闭Slave change master to master_host='192.168.40.147',master_user='root',master_password='1234',master_log_file='mysql-bin.000002',master_log_pos=120; start slave;#开启Slave # master_host: master主机IP地址 # master_user: master用户名 # master_password: masger用户使用的密码 # master_log_file: 对应master的File选项 # master_log_pas: 对应master的Position选项 1.9.4.4查看slave配置信息 show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.40.146 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 560 Relay_Log_File: localdomain02-relay-bin.000002 Relay_Log_Pos: 723 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: test Replicate_Ignore_DB: mysql 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: 560 Relay_Log_Space: 904 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 Master_UUID: b00baea9-204e-11e7-aadc-000c29379aff Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 1 row in set (0.00 sec)
mysql主从备份,读写分离
最新推荐文章于 2024-09-25 14:34:16 发布