MySQL主从复制
|
《MySQL主从复制》要点： 一、简述原理：
原理图 1、master记载二进制日志 2、slave的I/O线程读取master的二进制日志,并将其写入到中继日志中,SQL线程从中继日志中读取时间,并重放其中变乱,更新slave的数据 二、准备工作： 封闭防火墙 #server iptables stop 封闭开机自启 #chkconfig iptables off 封闭selinux #setenforce 0 在/etc/selinux/config 中,将SELINUX=enforcing改为SELINUX=disabled 同步光阴 #ntpdate 202.120.2.101 ======================================================= 三、安装mysql5.6 #cd /usr/local/src 解压mysql包 #tar -zxf MySQL-5.6.23-1.el6.x86_64.rpm-bundle.tar 用yum安装mysql,办理依赖关系 #yum install MySQL-shared-compat-5.6.23-1.el6.x86_64.rpm #yum install MySQL-server-5.6.23-1.el6.x86_64.rpm #yum install MySQL-client-5.6.23-1.el6.x86_64.rpm #yum install MySQL-devel-5.6.23-1.el6.x86_64.rpm #yum install MySQL-shared-5.6.23-1.el6.x86_64.rpm 创立数据目录 #mkdir -pv /home/mydata/data #chown -R mysql.mysql /home//mydata #chmod -R +w /home/mydata 四、改动配置文件 配置文件【主】 #cat /etc/my.cnf [mysqld] # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server,else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set,remove the # and set as required. # basedir = ..... datadir = /home/mydata/data port = 3306 socket = /var/lib/mysql/mysql.sock log-bin = master-bin server_id = 1 skip-external-locking key_buffer_size = 256M max_allowed_packet = 1M table_open_cache = 256 pid-file = /home/mydata/data/mysql.pid # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed,experiment to find the optimal values. # join_buffer_size = 128M sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 1M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size= 16M innodb_file_per_table = on thread_concurrency = 8 skip_name_resolve = on sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 配置文件【从】 [mysqld] # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server,remove the # and set as required. # basedir = ..... datadir = /home/mydata/data port = 3306 server_id = 2 pid-file = /home/mydata/data/mysql.pid relay-log = relay-bin socket = /var/lib/mysql/mysql.sock skip-external-locking key_buffer_size = 256M max_allowed_packet = 1M table_open_cache = 256 # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed,STRICT_TRANS_TABLES 初始化MySQL #/usr/bin/mysql_install_db --datadir=/usr/local/work/mydata --user=mysql 启动服务 #service mysql start 设置root暗码 #mysql>SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456'); 【主节点】授权复制权限账号给从节点 #mysql>grant replication client,replication slave on *.* to 'repuser'@'192.168.%.%' identified by 'reppasswd'; #mysql>flush privileges; 查看状态 #mysql>show master status; Slave_IO_Running: Yes Slave_SQL_Running: Yes 【从节点】指定主节点,复制账号 #mysql>change master to master_host='192.168.1.6',master_user='repuser',master_password='reppasswd',master_log_file='master-bin.000001',master_log_pos=120; 查看状态 #mysql>show slave statusG Slave_IO_Running: Yes Slave_SQL_Running: Yes 备注：这里只讲到mysql主从复制,但没有讲到如何实现高可用,可以应用keepalived来实现. 欢迎参与《MySQL主从复制》讨论，分享您的想法，编程之家PHP学院为您提供专业教程。 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
