理论知识
mysql 同步原理
mysql同步方案
读写分离方案
mysql linux 免安装配置
安装前必须删除原来的安装
需要检查 以下文件是否存在 ,如果存储则要删除之
/etc/my.cnf
/etc/init.d/mysqld
mysql 依赖的库
- shell> yum search libaio # search for info
- shell> yum install libaio # install library
创建mysql 与用户组,-s /bin/false 表示该用户不能登录
- shell> groupadd mysql
- shell> useradd -r -g mysql -s /bin/false mysql
解压安装包至指定目录
- shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz
- shell> ln -s full-path-to-mysql-VERSION-OS mysql
- shell> cd mysql
为 mysql 用户添加权限
- shell> chown -R mysql ./
- shell> chgrp -R mysql ./
- #创建data目录并添加权限
- shell> mkdir -p /data/mysql
- shell> chown -R mysql:mysql /data/mysql
拷贝配置文件
- shell> cp ${basedir}/support-files/my-medium.cnf /etc/my.cnf
- #修改配置
- [mysqld]
- basedir=/home/cbt/svr/mysql
- datadir=/data/mysql
- character-set-server=utf8
- shell> cp support-files/mysql.server /etc/init.d/mysql
初始化 mysql 库
- shell> ./scripts/mysql_install_db --user=mysql --basedir=/home/cbt/svr/mysql --datadir=/data/mysql
#添加环境变量
- shell> vi /etc/profile
- PATH=/home/cbt/svr/mysql/bin:$PATH
- export PATH
- #让刚才的修改生效
- shell> source /etc/profile
启动及其它配置 #启动数据库
- service mysql start
- #开机启动
- chkconfig mysqld on
- #初始化mysql的一些设置
- mysql_secure_installation
- #回车
- Enter current password for root (enter for none):
- #y,设置mysql的root密码
- Set root password?[Y/n] y
- #以下都yes
- Remove anonymous users?[Y/n] y
- Disallow root login remotely?[Y/n] y
- Remove test database and access to it?[Y/n] y
- Reload privilege tables now?[Y/n] y
- ThanksforusingMySQL!
允许远程登陆
- mysql> use mysql;
- mysql> select host,user,password from user;
- mysql> update user set password=password('123456') where user='root';
- mysql> update user set host='%' where user='root' and host='localhost';
- mysql> flush privileges;
安装时的一些错误
-bash: ./scripts/mysql_install_db: /usr/bin/perl: bad interpreter: 没有那个文件或目录
解决: yum -y install perl perl-devel
Installing MySQL system tables..../bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
解决:yum -y install libaio-devel
一主多从配置
master my.cnf 配置
- #需要同步的二进制数据库名;
- binlog-do-db=tuling
- #不同步的二进制数据库名,如果不设置可以将其注释掉;
- binlog-ignore-db=information_schema
- binlog-ignore-db=mysql
- binlog-ignore-db=personalsite
- binlog-ignore-db=test
- #以下参数可选----------
- #binlog 格式
- binlog-format=ROW
- log-bin=mysql-master-bin
- #slave更新时是否记录到日志中;
- log-slave-updates=true
- #开启半同步,需要另外安装插来支持
- rpl_semi_sync_master_enabled=ON
创建用户用于主从同步的数据库
- grant replication slave,super,reload on *.* to slave1@192.168.17.201 identified by '123456';
查看主节点状态
- mysql> show master status
- #在主库上查看已连接的slave主机
- mysql> show slave hosts;
- #查看所有binlog日志
- mysql> show binary logs;
- #查看所有binlog 事件
- mysql> show binlog events in 'mysql-bin.000003' from 145 \G;
slave my.cnf 配置-----
- server-id = 2
- log-bin=mysql-slave-bin
- replicate-do-db=tuling
- replicate-ignore-db=information_schema
- replicate-ignore-db=mysql
- replicate-ignore-db=personalsite
- replicate-ignore-db=test
slave 节点修改master 配置
- mysql>change master to master_host='192.168.0.101', master_user='slave1', master_password='123456' ,MASTER_AUTO_POSITION = 2887;
Slave 相关操作
- #启动slave
- mysql>start slave;
- mysql>stop slave;
- #查看 slave 状态
- show slave status\G;
- #跳过指定数量错误
- SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
- #查看 relaylog 事件
- show relaylog events in 'localhost-relay-bin.000019'
读写分离实现 360 Atlas
安装与卸载Atlas
atlas下载
链接:https://pan.baidu.com/s/1zXCYzayszOBCAxZPnLOMVw 提取码:2s8b
- #安装
- shell> rpm -i Atlas-2.2.1.el6.x86_64.rpm
- #卸载
- shell> rpm -e Atlas-2.2.1.el6.x86_64.rpm
安装目录在 /usr/local/mysql-proxy/ 当中
Atlass配置
- #Atlas后端连接的MySQL主库的IP和端口,可设置多项,用逗号分隔
- proxy-backend-addresses = 127.0.0.1:3306
- #Atlas后端连接的MySQL从库的IP和端口,@后面的数字代表权重,用来作负载均衡,若省略则默认为1,可设置多项,用逗号分隔
- #proxy-read-only-backend-addresses = 127.0.0.1:3305@1
- #用户名与其对应的加密过的MySQL密码,密码使用PREFIX/bin目录下的加密程序encrypt加密。用户密码所有主库和从库都必须一至
- pwds = root:/iZxz+0GRoA=
- #Atlas监听的工作接口IP和端口
- proxy-address = 0.0.0.0:1234
启动与关闭Atlas
- ./mysql-proxyd test start
- ./mysql-proxyd test stop
以代理方式进行管理员登录
- mysql -h127.0.0.1 -P2345 -uroot -proot