Mysql集群搭建1-linux上Mysql的安装与配置

1.Mysql集群的原理

  通过访问虚拟ip值，在主keepalived没有宕机的情况下访问主keepalived的值对应的mysql,主keepalived宕机的情况下访问从keepalived对应的mysql.两个服务器通过虚拟ip关联.

2.Linux上Mysql的安装

1、配置YUM源

     1.yum源配置

    yum install -y https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

    查看安装yum源成功

 yum repolist enabled | grep "mysql.*-community.*"

    可以修改 vim /etc/yum.repos.d/mysql-community.repo 源，改变默认安装的mysql版本。比如要安装5.6版本，将5.7源的enabled=1改 成        enabled=0。然后再将5.6源的enabled=0改成enabled=1即可。改完之后的效果如下所示：

2、安装MySQL

shell> yum install mysql-community-server

3、启动MySQL服务

shell> systemctl start mysqld

查看MySQL的启动状态

shell> systemctl status mysqld● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled) Active: active (running) since 五 2016-06-24 04:37:37 CST; 35min ago Main PID: 2888 (mysqld) CGroup: /system.slice/mysqld.service └─2888 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

6月 24 04:37:36 localhost.localdomain systemd[1]: Starting MySQL Server... ----启动成功

6月 24 04:37:37 localhost.localdomain systemd[1]: Started MySQL Server. ----启动成功

4、修改root本地登录密码

mysql安装完成之后，在/var/log/mysqld.log文件中给root生成了一个默认密码。通过下面的方式找到root默认密码，然后登录mysql进行修改：

shell> grep 'temporary password' /var/log/mysqld.log

shell> mysql -uroot -p默认密码mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';

修改配置文件exit

vi / etc/my.cnf

datadir=/home/mysql/mysqldate

socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

slow_query_log = on

slow-query-log-file = /var/log/slow.log

long_query_time=5

skip-name-resolve

innodb_file_per_table = 1

innodb_buffer_pool_size = 10G

innodb_log_file_size = 256M

max_connections = 2000

//mysql服务的id，不可重复

server_id = 2

log-bin = mysql-bin

binlog_format = mixed

read-only = 0

relay_log = /home/mysql/mysqlbinlog/mysql-relay-bin 作为同步作用的文件夹，必须手动创建，修改权限

log-slave-updates = on

auto-increment-offset=2 序列自增规则必须一个奇数一个偶数

auto-increment-increment=2 序列自增规则必须一个奇数一个偶数

4、添加同步账号

添加主从同步账户:

mysql > grant replication slave on * . * to 'repl' @ '192.168.10.12' identified by '123456' ;

mysql > flush privileges ;

查看主库的状态：

mysql > show master status;

配置同步信息:

mysql > change master to master_host = '192.168.10.12' ,master_port = 3306 ,master_user = 'repl' ,master_password = '123456' ,master_log_file = 'mysql-bin.000003' ,master_log_pos = 437 ;

查看主从配置信息:

mysql > start slave;mysql > show slave status\G;

显示如下状态则正常:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

为了避免出错可以将两个数据库 的密码设置的一致

出现的错误:

1.1045 错误码

1.找不到服务 在配置文件内添加 skip-name-resolve 以此跳过域名和DNS解析

错误原因:

2.1236 错误码

Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position; the first event 'mysql-bin.000011' at 424, the last event read from './mysql-bin.000011' at 4, the last byte read f

解决办法：

在主库中查找：

 /usr/local/mysql/bin/mysqlbinlog --start-position=144001931 /data/mysql/mysql-bin.001501 

/*!40010 SET @@session.max_insert_delayed_threads=0*/; 

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; 

DELIMITER /*!*/; 

# at 40019  --真正的开始位置

#130629 12:00:50 server id 2  end_log_pos 107  Start: binlog v 4, server v 5.5.15-log created 130629 12:00:50 

# Warning: this binlog is either in use or was not closed properly. 

BINLOG ' 

8lvOUQ8CAAAAZwAAAGsAAAABAAQANS41LjE1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA== 

'/*!*/; 

DELIMITER ; 

# End of log file 

ROLLBACK /* added by mysqlbinlog */; 

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

然后重新

change master to master_host='192.168.50.35',master_port=3306,master_user='repl',master_password='trsadmin123',master_log_file='mysql-bin.000011',master_log_pos=40019;

必须先reset slave;