一、MySQL的安装与配置（略）
二、MySQL主从复制
场景描述：
mysql-proxy代理服务器：192.168.163.131
主数据库服务器：192.168.163.145，MySQL已经安装，并且无应用数据。
从数据库服务器：192.168.163.144，MySQL已经安装，并且无应用数据。
.1 主服务器上进行的操作
/etc/my.cnf实例：
[mysql]
prompt= (\u@\h)[\d]>
[client]
port = 3306
socket = /tmp/mysql.sock
default_character_set = utf8
[mysqld]
user= mysql
port = 3306
socket = /tmp/mysql.sock
datadir = /home/mysql/data
pid_file = /var/run/mysqld/mysqld.pid
log-bin = /home/mysql/data/mysql-bin
log-error = /var/log/mysqld.log
server-id = 1
skip-name-resolve
relay-log = /home/mysql/data/mysql-relay-bin
log-slave-updates
slave-skip-errors = all
relay-log-index=relay-bin-index
slave-net-timeout=60
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
binlog_format = mixed
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_UNSIGNED_SUBTRACTION"
character_set_server = utf8
collation_server = utf8_bin
max_connections = 3000
max_connect_errors = 50
skip-external-locking
skip_name_resolve
lower_case_table_names=0
back_log = 128
table_open_cache = 512
open-files-limit = 65535
max_allowed_packet = 16M
thread_cache_size = 64
thread_concurrency = 8
binlog_cache_size = 1M
max_heap_table_size =1G
tmp_table_size = 500M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
query_cache_size = 128M
query_cache_limit = 4M
event_scheduler = ON
default-storage-engine = innodb
innodb_buffer_pool_size = 1G
innodb_additional_mem_pool_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_open_files=65535
innodb_data_home_dir = /home/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir= /home/mysql/data
innodb_log_files_in_group = 2
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout = 120
innodb_max_dirty_pages_pct = 90
innodb_thread_concurrency = 8
key_buffer_size = 64M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 2G
myisam_repair_threads = 1
myisam_recover
wait_timeout = 864000
interactive_timeout = 864000
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
#safe-updates
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
从库：
[mysql]
prompt= (\u@\h)[\d]>
[client]
port = 3306
socket = /tmp/mysql.sock
default_character_set = utf8
[mysqld]
user= mysql
port = 3306
socket = /tmp/mysql.sock
datadir = /home/mysql/data
pid_file = /var/run/mysqld/mysqld.pid
log-bin = /home/mysql/data/mysql-bin
log-error = /var/log/mysqld.log
server-id = 2
skip-name-resolve
relay-log = /home/mysql/data/mysql-relay-bin
log-slave-updates
slave-skip-errors = all
relay-log-index=relay-bin-index
#binlog-do-db=test
#replicate-do-db=test
slave-net-timeout=60
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
binlog_format = mixed
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_UNSIGNED_SUBTRACTION"
character_set_server = utf8
collation_server = utf8_bin
max_connections = 3000
max_connect_errors = 50
skip-external-locking
skip_name_resolve
lower_case_table_names=0
back_log = 128
table_open_cache = 512
open-files-limit = 65535
max_allowed_packet = 16M
thread_cache_size = 64
thread_concurrency = 8
binlog_cache_size = 1M
max_heap_table_size =1G
tmp_table_size = 500M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
query_cache_size = 128M
query_cache_limit = 4M
event_scheduler = ON
default-storage-engine = innodb
innodb_buffer_pool_size = 1G
innodb_additional_mem_pool_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_open_files=65535
innodb_data_home_dir = /home/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir= /home/mysql/data
innodb_log_files_in_group = 2
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout = 120
innodb_max_dirty_pages_pct = 90
innodb_thread_concurrency = 8
key_buffer_size = 64M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 2G
myisam_repair_threads = 1
myisam_recover
wait_timeout = 864000
interactive_timeout = 864000
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
#safe-updates
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
启动mysql服务
/opt/mysql/init.d/mysql start
通过命令行登录管理MySQL服务器
/opt/mysql/bin/mysql -uroot -p’new-password’
授权给从数据库服务器192.168.163.144
mysql> GRANT REPLICATION SLAVE ON *.* to ‘slave’@’192.168.163.144’ identified by ‘123456’;
查询主数据库状态
Mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000005 | 261 | | |
+——————+———-+————–+——————+
记录下 FILE 及 Position 的值，在后面进行从服务器操作的时候需要用到。
2.2 配置从服务器
修改从服务器的配置文件/opt/mysql/etc/my.cnf
将 server-id = 1修改为 server-id = 10，并确保这个ID没有被别的MySQL服务所使用。
启动mysql服务
/opt/mysql/init.d/mysql start
通过命令行登录管理MySQL服务器
/opt/mysql/bin/mysql -uroot -p’new-password’
执行同步SQL语句
mysql> change master to
master_host=’192.168.163.145’,
master_user=’slave’,
master_password=’123456’,
master_log_file=’mysql-bin.000005’,
master_log_pos=261;
正确执行后启动Slave同步进程
mysql> start slave;
主从同步检查
mysql> show slave status\G
==============================================
**************** 1. row *******************
Slave_IO_Running: YES
Slave_SQL_Running: YES
Replicate_Do_DB:
……………省略若干……………
Master_Server_Id: 1
1 row in set (0.01 sec)
==============================================
其中Slave_IO_Running 与 Slave_SQL_Running 的值都必须为YES，才表明状态正常。
如果主服务器已经存在应用数据，则在进行主从复制时，需要做以下处理：
(1)主数据库进行锁表操作，不让数据再进行写入动作
mysql> FLUSH TABLES WITH READ LOCK;
(2)查看主数据库状态
mysql> show master status;
(3)复制数据文件
将主服务器的数据文件（整个/opt/mysql/data目录）复制到从服务器，建议通过tar归档压缩后再传到从服务器解压。
(4)取消主数据库锁定
mysql> UNLOCK TABLES;
2.3 验证主从复制效果
主服务器上的操作
在主服务器上创建数据库first_db
mysql> create database first_db;
Query Ok, 1 row affected (0.01 sec)
在主服务器上创建表first_tb
mysql> create table first_tb(id int(3),name char(10));
Query Ok, 1 row affected (0.00 sec)
在主服务器上的表first_tb中插入记录
mysql> insert into first_tb values (001,’myself’);
Query Ok, 1 row affected (0.00 sec)
在从服务器上查看
mysql> show databases;
=============================
+——————–+
| Database |
+——————–+
| information_schema |
| first_db |
| mysql |
| performance_schema |
| test |
+——————–+
5 rows in set (0.01 sec)
=============================
数据库first_db已经自动生成
mysql> use first_db
Database chaged
mysql> show tables;
=============================
+——————–+
| Tables_in_first_db |
+——————–+
| first_tb |
+——————–+
1 row in set (0.02 sec)
=============================
数据库表first_tb也已经自动创建
mysql> select * from first_tb;
=============================
+——+——+
| id | name |
+——+——+
| 1 | myself |
+——+——+
1 rows in set (0.00 sec)
=============================
记录也已经存在
由此，整个MySQL主从复制的过程就完成了，接下来，我们进行MySQL读写分离的安装与配置。
三、MySQL读写分离:
检查系统所需软件包
通过 rpm -qa | grep name 的方式验证以下软件包是否已全部安装。
yum install -y gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libmcrypt* libtool* flex* pkgconfig*
libevent* glib*
http://www.lua.org/ftp/ lua的官网
可现在系统查看是否有lua插件
rpm -qa |grep lua
lua-5.1.4-15.el7.x86_64
https://cdn.mysql.com/Downloads/MySQL-Proxy/mysql-proxy-0.8.4-linux-glibc2.3-x86-64bit.tar.gz） mysql-proxy下载地址
htttp://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/
解压：
tar xzvf mysql-proxy-0.8.1-linux-rhel5-x86-32bit.tar.gz
mv mysql-proxy-0.8.1-linux-rhel5-x86-32bit /usr/localmysql-proxy
PROXY_PATH=/opt/mysql-proxy/bin //定义mysql-proxy服务二进制文件路径
PROXY_OPTIONS=”–admin-username=root \ //定义内部管理服务器账号
–admin-password=password \ //定义内部管理服务器密码
–proxy-read-only-backend-addresses=192.168.10.131:3306 \ //定义后端只读从服务器地址
–proxy-backend-addresses=192.168.10.130:3306 \ //定义后端主服务器地址
–admin-lua-script=/opt/mysql-proxy/lib/mysql-proxy/lua/admin.lua \ //定义lua管理脚本路径
–proxy-lua-script=/opt/mysql-proxy/scripts/rw-splitting.lua” \ //定义lua读写分离脚本路径
PROXY_PID=/opt/mysql-proxy/run/mysql-proxy.pid //定义mysql-proxy PID文件路径
$NICELEVEL $PROXY_PATH/mysql-proxy $PROXY_OPTIONS \
–daemon \ //定义以守护进程模式启动
–keepalive \ //使进程在异常关闭后能够自动恢复
–pid-file=$PROXY_PID \ //定义mysql-proxy PID文件路径
–user=mysql \ //以mysql用户身份启动服务
–log-level=warning \ //定义log日志级别，由高到低分别有(error|warning|info|message|debug)
–log-file=/opt/mysql-proxy/log/mysql-proxy.log //定义log日志文件路径
==============================================
cp mysql-proxy /usr/local/mysql-proxy/init.d/
chmod +x /usr/local/mysql-proxy/init.d/mysql-proxy
mkdir /usr/local/mysql-proxy/run
mkdir /usr/local/mysql-proxy/logs
mkdir /usr/local/mysql-proxy/scripts
配置并使用rw-splitting.lua读写分离脚本
最新的脚本我们可以从最新的mysql-proxy源码包中获取
cd /opt/install
wget http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.1.tar.gz
tar xzvf mysql-proxy-0.8.1.tar.gz
cd mysql-proxy-0.8.1
cp lib/rw-splitting.lua /usr/local/mysql-proxy/scripts
修改读写分离脚本rw-splitting.lua
修改默认连接，进行快速测试，不修改的话要达到连接数为4时才启用读写分离
vim /opt/mysql-proxy/scripts/rw-splitting.lua
=============================
— connection pool
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 1, //默认为4
max_idle_connections = 1, //默认为8
is_debug = false
}
end
=============================
修改完成后，启动mysql-proxy
/opt/mysql-proxy/init.d/mysql-proxy start
/usr/local/mysql-proxy/lua/admin.lua rw-splitting.lua 存放管理lua 和rw分离lua。
vim cat /etc/mysql-proxy.cnf
[mysql-proxy]
user=root
plugins=admin,proxy
admin-address=192.168.163.131:4041
admin-username=proxy
admin-password=123.com
admin-lua-script=/usr/local/mysql-proxy/lua/admin.lua
proxy-address=192.168.163.131:3306
proxy-read-only-backend-addresses=192.168.163.144:3306
proxy-backend-addresses=192.168.163.145:3306
proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log
log-level=debug
daemon=true
keepalive=true
/usr/local/mysql-proxy/bin/mysql-proxy -V 查看版本
/usr/local/mysql-proxy/bin/mysql-proxy --help-all 帮助命令
/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf 启动
cat /usr/local/mysql-proxy/logs/mysql-proxy.log 查看日志
netstat -tnlp 监控端口是否启动
mysql -uproxy -h 192.168.163.131 -P 4041 -p123.com 登录验证
select * from backends; 查看后端服务器的状态。
mysql -uproxy -p123.com -h192.168.163.131 -P 3306 -e "select user,host from mysql.user" 直接可以在命令行测试。可执行多次来验证读写分离。
mysql -uproxy -h 192.168.163.131 -P 3306 -p123.com 代理端口登录。
156
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
