|
|
一、源码安装mysql5.6.20
1.编译环境安装
yum install wget gcc* make openssl openssl-devel openssl-clients -y && yum groupinstall " Development tools" -y
2.源码下载(软件见Linux部署源码包)
wget -P /usr/local/src/ http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.20.tar.gz
wget -P /usr/local/src/ http://down1.chinaunix.net/distfiles/cmake-2.8.10.2.tar.gz
3.源码安装cmake编译环境
cd /usr/local/src/ && tar xf cmake-2.8.10.2.tar.gz
cd cmake-2.8.10.2 && ./configure --prefix=/usr/local/cmake && make && make install
4.源码安装mysql-5.6.20
groupadd mysql
useradd -r -g mysql -s /etc/nologin mysql #(创建mysql用户不需要密码和登录系统)
mkdir -p /home/mysql/data && mkdir /home/mysql/var #
cd /usr/local/src/ && tar xf mysql-5.6.20.tar.gz
cd mysql-5.6.20
cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/home/mysql/data -DSYSCONFDIR=/etc -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DWITH_SSL=system -DWITH_ZLIB=system -DWITH_LIBWRAP=0 -DMYSQL_UNIX_ADDR=/home/mysql/var/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci
make && make install
环境变量配置
vim /etc/profile
在末行添加export PATH=$PATH:/usr/sbin/:/usr/local/mysql/bin
保存退出执行：source /etc/profile
5.初始化数据库
/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/home/mysql/data --user=mysql --default-storage-engine=MyISAM
6.配置启动脚本
cp /usr/local/src/mysql-5.6.20/support-files/mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql
chkconfig --add mysql && chkconfig mysql on
二、mysql主从复制设置
1.主mysql配置文件my.cnf
=====================================================
[client]
default-character-set=utf8
#auto-rehash
socket = /home/mysql/var/mysql.sock
[mysqld]
slave-skip-errors=1022,1032,1062
skip-name-resolve
basedir = /usr/local/mysql
datadir = /home/mysql/data
socket = /home/mysql/var/mysql.sock
character-set-server=utf8
collation-server=utf8_general_ci
back_log = 300
max_connections = 5000
max_connect_errors = 30
table-definition-cache=2000
table-open-cache=4000
max_allowed_packet = 32M
max_heap_table_size = 128M
sort_buffer_size = 16M
join_buffer_size = 16M
#thread_cache_size = 16
#thread_concurrency = 8
query_cache_size = 128M
query_cache_limit = 4M
ft_min_word_len = 8
default-storage-engine=MYISAM
thread_stack = 512K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 128M
slow-query-log
long_query_time = 6
#master config
# grant replication slave, replication client on *.* to 'root'@'192.168.1.131'> # ref : http://blog.csdn.net/seteor/article/details/17261733
server-id = 1
log-bin=mysqlmaster-bin
#binlog-do-db = cad #指定需要进行主从的数据库
binlog_cache_size = 4M
binlog_format=mixed
expire_logs_days=3
#binlog-ignore-db=test #不记录test库的binlog
#replicate-ignore-db=test #不复制test库的binlog
sync_binlog=1
sql_mod=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
lower_case_table_names=1
#slave config
#server_id=2
#binlog-do-db = cad
#binlog-ignore-db=test #不记录test库的binlog
#replicate-ignore-db=test #不复制test库的binlog
#log-bin=mysql-bin
#binlog_cache_size = 1M
#binlog_format=mixed
#expire_logs_days=3
key_buffer_size = 128M
read_buffer_size = 8M
read_rnd_buffer_size = 64M
bulk_insert_buffer_size = 256M
myisam_sort_buffer_size = 256M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
skip-federated
#innodb config
innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size = 512M
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
[mysqldump]
quick
max_allowed_packet = 32M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 2048M
sort_buffer_size = 2048M
read_buffer = 32M
write_buffer = 32M
[myisamchk]
key_buffer = 2048M
sort_buffer_size = 2048M
read_buffer = 32M
write_buffer = 32M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192
=====================================================
2.从mysql配置文件my.cnf
=====================================================
[client]
default-character-set=utf8
#auto-rehash
socket = /home/mysql/var/mysql.sock
[mysqld]
slave-skip-errors=1022,1032,1062
skip-name-resolve
basedir = /usr/local/mysql
datadir = /home/mysql/data
socket = /home/mysql/var/mysql.sock
character-set-server=utf8
collation-server=utf8_general_ci
back_log = 300
max_connections = 5000
max_connect_errors = 30
table-definition-cache=2000
table-open-cache=4000
max_allowed_packet = 32M
max_heap_table_size = 128M
sort_buffer_size = 16M
join_buffer_size = 16M
#thread_cache_size = 16
#thread_concurrency = 8
query_cache_size = 128M
query_cache_limit = 4M
ft_min_word_len = 8
default-storage-engine=MYISAM
thread_stack = 512K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 128M
slow-query-log
long_query_time = 6
#master config
# grant replication slave, replication client on *.* to 'root'@'192.168.1.131'> # ref : http://blog.csdn.net/seteor/article/details/17261733
#server-id = 1
#log-bin=mysql-bin
#binlog_cache_size = 4M
#binlog_format=mixed
#expire_logs_days=3
#binlog-ignore-db=test
#replicate-ignore-db=test
#slave config
server_id=2
#binlog-do-db = cad #指定需要做主从的数据库，如果不指定则默认全库主从
#binlog-ignore-db=test #不记录binlog
#replicate-ignore-db=test #不复制test库的binlog
log-bin=mysqlslave-bin
binlog_cache_size = 1M
binlog_format=mixed
expire_logs_days=3
sync_binlog=1
key_buffer_size = 128M
read_buffer_size = 8M
read_rnd_buffer_size = 64M
bulk_insert_buffer_size = 256M
myisam_sort_buffer_size = 256M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
skip-federated
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
lower_case_table_names=1
#innodb config
innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size = 512M
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
[mysqldump]
quick
max_allowed_packet = 32M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 2048M
sort_buffer_size = 2048M
read_buffer = 32M
write_buffer = 32M
[myisamchk]
key_buffer = 2048M
sort_buffer_size = 2048M
read_buffer = 32M
write_buffer = 32M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192
=====================================================
3.启动主从数据库并将其加入开启自启动项
service mysql start
chkconfig mysql on
chkconfig --list mysql #查看mysql的开机项设置
4.主从配置（参考：http://blog.chinaunix.net/xmlrpc.php?r=blog/article&uid=26267891&id=3856859）
http://www.linuxidc.com/Linux/2014-06/103752.htm
4.1 主mysql服务器创建同步的账号：（ip地址从mysql所属IP）
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'10.124.156.237'> Query OK, 0 rows affected (0.13 sec)
mysql> flush privileges;
4.2 主mysql服务器查看master状态，获取二进制日志名和偏移量
mysql> show master status\G
*************************** 1. row ***************************
File: mysqlmaster-bin.000004
Position: 396
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
（备注：show master status\G; 报错ERROR: No query specified 属于语法错误）
4.3 主mysql停止数据库操作，对数据库进行导出操作，然后导入到从mysql
#首先添加一个读锁保证数据库的一致性
mysql> flush tables with read lock;
mysql> quit;
mysqldump -h127.0.0.1 -uroot -p -P 3306 --all-databases --triggers --routines --events> /opt/all.sql
#最后恢复Master机器的读锁
mysql> unlock tables;
4.4 从mysql进行数据导入(mysqldump可以在从服务器做：-h master_ip)
mysql -uroot -p -h127.0.0.1 -P3306 < /opt/all.sql
4.5 在从mysql服务器上设置数据同步的数值，该步一些参数是从4.1和4.2得到的
mysql> change master to master_host = '10.124.165.254',master_user='slave',master_password='yzkj2015',master_log_file='mysqlmaster-bin.000004',master_log_pos=396;
4.6 启动从数据库复制线程，并查询从数据库的复制线程状态
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.124.165.254
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqlmaster-bin.000004
Read_Master_Log_Pos: 1021
Relay_Log_File: mysql_S-relay-bin.000005
>
> Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 1021
> 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: 1
Master_UUID: 709e2dcf-0b51-11e5-9754-286ed489d502
Master_Info_File: /home/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all> 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)
查询结果中：Slave_IO_Running: Yes 同时出现才证明主从设置成功
Slave_SQL_Running: Yes
三、多主一从类型的主从设置
1.背景介绍：
公司线上跑着多个业务型mysql数据库，最初考虑的是避免有很多连锁反应的数据库崩溃，于是就有了多个数据库同时进行主从热备的需求。这里采用mysql数据库本身自带的一种多实例数据库设置。具体的安装请参照第一部分进行编译安装，下面我将从初始化到多实例数据库的启动开始写起。
2.多实例数据库的初始化
2.1 创建多实例数据目录
mkdir -p /home/mysql/data/data_3306/ /home/mysql/data/data_3306/socket
mkdir -p /home/mysql/data/data_3307/ /home/mysql/data/data_3307/socket
mkdir -p /home/mysql/data/mysqld_multi_logs
chown –R mysql.mysql /home/mysql/
2.2 my.cnf配置文件
======================================
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
log = /home/mysql/data/mysqld_multi_logs/mysqld_multi.log
#user = root
#password = password
[mysqld1]
socket = /home/mysql/data/data_3306/socket/mysql.sock
port = 3306
pid-file = /home/mysql/data/data_3306/3306.pid
datadir = /home/mysql/data/data_3306
user = mysql
skip-name-resolve
lower_case_table_names=1
innodb_file_per_table=1
back_log = 50
max_connections = 300
max_connect_errors = 1000
table_open_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 2M
max_heap_table_size = 64M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 64
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default-storage-engine = MYISAM
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
log-bin=mysqlslave-bin
binlog_format=mixed
slow_query_log
long_query_time = 1
#slave
server_id=2
#binlog-do-db = cad
#binlog-ignore-db=test #不记录binlog
#replicate-ignore-db=test #不复制test库的binlog
log-bin=mysqlslave-bin
binlog_cache_size = 1M
binlog_format=mixed
expire_logs_days=3
sync_binlog=1
key_buffer_size = 128M
read_buffer_size = 8M
read_rnd_buffer_size = 64M
bulk_insert_buffer_size = 256M
myisam_sort_buffer_size = 256M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
skip-federated
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
lower_case_table_names=1
#innodb config
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 200M
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 60
innodb_lock_wait_timeout = 120
[mysqld2]
socket = /home/mysql/data/data_3307/socket/mysql.sock
port = 3307
pid-file = /home/mysql/data/data_3307/3307.pid
datadir = /home/mysql/data/data_3307
user = mysql
skip-name-resolve
lower_case_table_names=1
innodb_file_per_table=1
back_log = 50
max_connections = 300
max_connect_errors = 1000
table_open_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 2M
max_heap_table_size = 64M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 64
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default-storage-engine = MYISAM
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
log-bin=mysql-bin
binlog_format=mixed
slow_query_log
long_query_time = 1
#slave
server_id=2
#binlog-do-db = cad
#binlog-ignore-db=test #不记录binlog
#replicate-ignore-db=test #不复制test库的binlog
log-bin=mysqlslave-bin
binlog_cache_size = 1M
binlog_format=mixed
expire_logs_days=3
sync_binlog=1
key_buffer_size = 128M
read_buffer_size = 8M
read_rnd_buffer_size = 64M
bulk_insert_buffer_size = 256M
myisam_sort_buffer_size = 256M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
skip-federated
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
lower_case_table_names=1
#innodb config
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 200M
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 60
innodb_lock_wait_timeout = 120
[mysqldump]
quick
max_allowed_packet = 256M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192
============================================
2.3 多实例初始化
/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/home/mysql/data/data_3306 --user=mysql --default-storage-engine=MyISAM
/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/home/mysql/data/data_3307 --user=mysql --default-storage-engine=MyISAM
3. 多实例数据库的启动与关闭
mysqld_multi start 1,2
mysqld_multi stop 1,2
4. 数据库的连接
1)本地连接：
mysql -S /home/mysql/data/data_3306/socket/mysql.sock #第一个实例连接
mysql -S /home/mysql/data/data_3307/socket/mysql.sock #第二个实例连接
2)远程连接：
mysql –h10.124.156.237 –P 3306 –u root –p #指定端口号连接
mysql –h10.124.156.237 –P 3307 –u root –p
参考连接：MySQL启多个实例 http://www.cnblogs.com/acpp/archive/2010/08/09/1795466.html
http://blog.chinaunix.net/uid-20639775-id-3438560.html
四、问题解决(参照err日志)
1. Slave_IO_Running: NO
Slave_SQL_Running: Yes
mysql replication 中slave机器上有两个关键的进程，死一个都不行，一个是slave_sql_running，一个是Slave_IO_Running，一个负责与主机的io通信，一个负责自己的slave mysql进程。
出现上面所述情况，可能有三种情况：1)网络有问题，连接不上；2)配置my.cnf可能有问题；3)授权的问题，replication slave和file权限是必须的。
详情参照：http://www.iyunv.net/article/27220.htm
我这里遇到的问题是在二 4.5步骤上少输入一个参数:master_password='yzkj2015'
2. Slave_IO_Running: connecting
Slave_SQL_Running: Yes
导致lave_IO_Running 为connecting 的原因主要有以下 3 个方面：
1)、网络不通
2)、密码不对
3)、pos不对
可以参考这个连接：http://blog.csdn.net/i_bruce/article/details/17055135
我这里出现的原因是在my.cnf文件中没有使用这个参数：
[mysqld]
skip-name-resolve
所以导致数据库远程连接过慢，一直处于一个正在连接的状态。
参考这个连接：解决远程连接mysql很慢的方法(mysql_connect 打开连接慢) http://www.iyunv.net/article/27616.htm
3./usr/local/mysql/bin/mysqlbinlog: unknown variable 'default-character-set=utf8'
在解决一些问题的时候，我们会去查看一下日志文件，这样就使用mysqlbinlog命令。可惜遇到上述问题，原因是我在my.cnf配置文件中设置了这个参数：
[mysqld]
default-character-set=utf8
解决的方法就是利用执行mysqlbinlog文件的时候只检查my.cnf文件，而我们修改my.cnf文件之后不重启是不会生效的，所以临时修改my.cnf文件将上述参数临时注释掉，但最后执行完命令之后不要忘记再修改回来。
具体参照连接：mysqlbinlog 查看mysql bin 日志 http://blog.csdn.net/gnufre/article/details/6955169
连接中提到的第二种方法没有实现
|
|
|
QQ群⑧：
窥视卡
雷达卡
发表于 2018-10-3 13:23:49
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡