mysql5.6 配置 命令_mysql5.6安装和主从配置

一、下载安装，在mysql主和从上面安装

1、下载mysql

cd /usr/local/src

tar -zxvf mysql-5.6.14.tar.gz

cd mysql-5.6.14

yum -y install make gcc-c++ cmake bison-devel ncurses-devel

cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql5.6 -DMYSQL_DATADIR=/data/mysql5.6/data -DSYSCONFDIR=/etc -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.sock -DMYSQL_TCP_PORT=3306 -DENABLED_LOCAL_INFILE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci

make && make install

二、创建数据目录配置文件

1、初始化工作

mkdir -p /data/mysql5.6/data

useradd mysql -s /sbin/nologin

passwd mysql

chown -R mysql.mysql /data/mysql5.6

cd /usr/local/mysql.5.6/

cp support-files/my-default.cnf /etc/my.cnf

cp support-files/mysql.server /etc/init.d/mysqld

ln -s ./bin* /usr/local/bin

./scripts/mysql_install_db --basedir=/usr/local/mysql5.6/ --datadir=/data/mysql5.6/data/ --user=mysql

三、mysql主配置

1、配置文件

shell>vim /etc/my.cnf

server-id = 1                    #一定不能和slave上的server-id相同，必须唯一

log-bin=master-bin.log

sync_binlog=1

innodb_buffer_pool_size = 5600M

innodb_flush_log_at_trx_commit=1

sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO

lower_case_table_names=1

log_bin_trust_function_creators=1

binlog_format = row

slave_parallel_workers=4          #--开启基于库的多线程复制默认0不开启

binlog_cache_size = 8M

max_binlog_size = 50M

max_binlog_cache_size = 100M

expire_logs_days = 1

log-slave-updates=true

rpl_semi_sync_master_enabled=ON

service mysqld start

2、设置主从用户

mysql -uroot -p

grant replication slave on *.* to 'repl'@'192.168.10.2' identified by 'repl';

show master status;             #记录master上的logfile  和position

四、mysql从设置

1、配置文件

server-id = 2                    #一定不能和master上的server-id相同

log-bin = slave-bin.log

sync_binlog = 1

innodb_buffer_pool_size = 512M

innodb_flush_log_at_trx_commit = 1

sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO

lower_case_table_names = 1

log_bin_trust_function_creators = 1

binlog_format = row

binlog_cache_size = 8M

max_binlog_size = 50M

max_binlog_cache_size = 100M

expire_logs_days = 1

slave_parallel_workers=4

max_relay_log_size = 50M

relay_log_purge = 1

relay_log_recovery = 1

master_verify_checksum = 1        #--主事件校验

slave_sql_verify_checksum = 1     #--从事件校验

slave_allow_batching = 1

log-slave-updates=true

service mysqld start

2、开启从库同步线程

mysql -uroot -p

change master to master_host='192.168.10.1',master_user='repl',master_password='repl',master_log_file='slave-bin.000003',master_log_pos=5569;

start slave;

show slave status\G;

#Slave_IO_Running: Yes

#Slave_SQL_Running: Yes

上面输出两项均为yes则正常

五、配置mysql 主服务器半同步

1、修改配置文件加入参数

rpl_semi_sync_master_enabled=ON

2、进入mysql命令行安装半同步插件

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

show global viriables like 'rpl_semi_%'

+------------------------------------+-------+

| Variable_name                      | Value |

+------------------------------------+-------+

| rpl_semi_sync_master_enabled       | ON    |

| rpl_semi_sync_master_timeout       | 10000 |

| rpl_semi_sync_master_trace_level   | 32    |

| rpl_semi_sync_master_wait_no_slave | ON    |

+------------------------------------+-------+

#rpl_semi_sync_master_enabled 为ON则安装成功

重启mysql master

servive mysqld restart

六、配置mysql 从服务器半同步

1、修改配置文件加入参数

rpl_semi_sync_slave_enabled=ON

2、进入mysql命令行安装半同步插件

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

show globle viriables like 'rpl_semi_%'

#rpl_semi_sync_slave_enabled 为ON则安装成功

+---------------------------------+-------+

| Variable_name                   | Value |

+---------------------------------+-------+

| rpl_semi_sync_slave_enabled     | ON    |

| rpl_semi_sync_slave_trace_level | 32    |

+---------------------------------+-------+

完成。