linux mysql 单机主从_centos7 单机部署mysql 主从

安装：

1 下载 mysql5.7 二进制压缩包

wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz

2 解压

tar zxvf mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz -C /usr/local/

mv mysql-5.7.24-linux-glibc2.12-x86_64 /usr/local/mysql

3 创建mysql 用户

useradd -r -s /sbin/nologin mysql

4 在mysql 二进制包目录中创建mysql-files目录(mysql 数据导入/导出的目录)

mkdir -v /usr/local/mysql/mysql-files

5 创建多实例数据目录

mkdir -vp /data/mysql_data{1..2}

6 修改目录权限

chown mysql.mysql /data -R

chown mysql.mysql /usr/local/mysql -R

7 配置my.cnf 文件

[mysqld_multi]

mysqld = /usr/local/mysql/bin/mysqld

mysqladmin = /usr/local/mysql/bin/mysqladmin

log = /tmp/mysql_multi.log

user = root

pass = 123456

[mysqld]

explicit_defaults_for_timestamp=true

skip-name-resolve

[mysqld1]

# 设置数据目录　[多实例中一定要不同]

datadir = /data/mysql_data1

# 设置sock存放文件名　[多实例中一定要不同]

socket = /tmp/mysql.sock1

# 设置监听开放端口　[多实例中一定要不同]

port = 3306

# 设置运行用户

user = mysql

# 关闭监控

performance_schema = off

# 设置innodb 缓存大小

innodb_buffer_pool_size = 32M

# 设置监听IP地址

bind_address = 0.0.0.0

# 关闭DNS 反向解析

skip-name-resolve = 0

#开启主从复制，主库的配置

#指定主库serverid

server-id=101

log-bin=master-bin #bin-log前缀

log-bin-index=master-bin.index #bin-log-index前缀

binlog_format=ROW #binlog格式 (STATEMENT,ROW,FIXED)，这里主要为了后续要测试canal，所以用到了ROW，具体格式区别，自行搜索

binlog-ignore-db=mysql #不需要同步给从库的库

binlog-ignore-db=sys #不需要同步给从库的库

binlog-ignore-db=information_schema #不需要同步给从库的库

binlog-ignore-db=performance_schema #不需要同步给从库的库

[mysqld2]

datadir = /data/mysql_data2

socket = /tmp/mysql.sock2

port = 3307

user = mysql

performance_schema = off

innodb_buffer_pool_size = 32M

bind_address = 0.0.0.0

skip-name-resolve = 0

server-id=102

8 初始化实例 初始化完成后自带随机密码在日志中

[root@master src]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data1

[root@master src]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_data2

2019-11-02T05:17:00.179360Z 0 [Warning] InnoDB: New log files created, LSN=45790

2019-11-02T05:17:00.322304Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2019-11-02T05:17:00.403884Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: ff8a0dde-fd2f-11e9-bff0-b82a72ceceea.

2019-11-02T05:17:00.410931Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2019-11-02T05:17:00.412000Z 1 [Note] A temporary password is generated for root@localhost: Pp!MtYkG=1IH

2019-11-02T05:17:02.561842Z 1 [Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.

2019-11-02T05:17:02.561943Z 1 [Warning] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.

2019-11-02T05:17:02.561978Z 1 [Warning] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.

2019-11-02T05:17:02.562025Z 1 [Warning] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.

2019-11-02T05:17:02.562049Z 1 [Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.

2019-11-02T05:17:02.562080Z 1 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.

2019-11-02T05:17:02.562184Z 1 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.

2019-11-02T05:17:02.562233Z 1 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.

9 复制多实例脚本到服务管理目录下

cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/mysqld_multi

10 添加脚本权限

chmod +x /etc/init.d/mysqld_multi

11 添加进service服务管理

chkconfig --add mysqld_multi

12 mysqld_multi 多实例管理

查看多实例：

/etc/init.d/mysqld_multi report

启动多实例：

/etc/init.d/mysqld_multi start

启动某个实例

/etc/init.d/mysqld_multi start 1

停止mysql服务

/etc/init.d/mysqld_multi stop (不管用，需要执行以下操作才能生效)

登录进入mysql实例

mysql -uroot -p -S  /tmp/mysql.sock1

mysql>grant shutdown on *.* to 'root'@'localhost' identified by '123456';

mysql>flush privileges;

kill -9 mysql_pid 就可以了

报错处理：

如果发现有以下报错 加个环境变量就可以了

vim /etc/profile

export PATH=/usr/local/mysql/bin:$PATH

source /etc/profile

[root@localhost /]# /usr/local/mysql/bin/mysqld_multi start 1,2,3

WARNING: my_print_defaults command not found.

Please make sure you have this command available and

in your path. The command is available from the latest

MySQL distribution.

ABORT: Can't find command 'my_print_defaults'.

This command is available from the latest MySQL

distribution. Please make sure you have the command

in your PATH.

安装完mysql 之后，登陆以后，不管运行任何命令，总是提示这个 ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. ,由于初始化各实例的时候采用了系统生成的密码，不方便记忆和操作

mysql> SET PASSWORD = PASSWORD('123456');

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

主从配置：

查询主库状态：

执行SQL语句查询状态：需要记录下Position值，需要在从库中设置同步起始值。

mysql>show master status;

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| master-bin.000006 | 154 | | mysql,sys,information_schema,performance_schema | |

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

1 row in set (0.00 sec)

在主库上创建同步用户：

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

mysql>flush privileges;

从库上执行：

mysql> change master to

-> master_host='localhost',

-> master_user='slave',

-> master_password='123456',

-> master_log_file='master-bin.000006',

-> master_log_pos=154;

Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> start slave; # 开启slave

Query OK, 0 rows affected (0.01 sec)

mysql> show slave status;