Linux MySQL安装(服务器架设篇)

mysql安装总结

linux安装方式

目前的Linux MySQL安装方式有4种:

  1. 源代码编译安装
  2. 二进制通用版安装(linux 通用压缩包)
  3. 具体平台的包文件安装(redhat/cent是rpm包,debain/ubuntu是deb包,其他的linux发行版也有对应的专用包)
  4. 包管理器安装(redhat/cent使用yum,debain/ubuntu使用apt-get)

上面4种安装方式具体的安装难度是递减的,越靠后越简单,而安装的灵活性却也是递减的,越靠后安装的可自主性越低

windows安装方式

目前的Linux MySQL安装方式有2种:

  1. windows/windows server软件版(类似于linux上的第三种)
  2. windows/windows server二进制解压版(类似于linux上的第二种)

一般第一种是windows用户开发时用的多一些,而windows server用户一般喜欢二进制解压版,主要是这种方式不需要安装windows图形化桌面,配置灵活,当然这种方式难度是大一点的。

linux安装过程

使用源代码编译安装的方式,一般适合高级用户使用,可以最大限度的利用机器的硬件,获得最适合机器的安装结果。但是编译过程繁琐而且很容易出现错误,本文展示不讲

使用具体平台的安装包或者包管理软件安装的方式是比较简单的,一般几行命令就基本完成了安装,安装配置和目录等细节是采用默认的配置,具体的要看安装包或者包管理器的具体情况,一般安装过程用户是难以干预也不需要干预的

本文讲的Linux 二进制通用包安装方式是所有Linux都可以使用的,难度介于上两者之间,而且安装过程灵活可配置。本文以目前公司的平台为参考在windows10 VirtualBox开虚拟机进行演示。虚拟机linux为cent os6.9 64bit 单核1G处理器1G RAM,MySQL为mysql5.6。软件版本完全与公司当前的测试/正式环境一致。

废话不多讲,我们开始吧

环境 版本
RHEL/CentOS 7.x
MySQL 8.x

先卸载干净CentOS上面自带的可能存在的MySQL组件或者旧版本

1
2
3
4
5
6
7
8
9
# 查看系统是否有mysql预装,看来的确是有的
[root@localhost imuser]# whereis mysql
mysql: /usr/lib64/mysql /usr/share/mysql
# 查看有哪些mysql组件是系统自带的
[root@localhost imuser]# rpm -qa|grep mysql
mysql-libs-5.1.71-1.el6.x86_64
# 卸载 将需要删除的依赖也一并删除
[root@localhost imuser]# rpm -e --nodeps mysql-libs-5.1.71-1.el6.x86_64
# 卸载完后再次检查一遍,没有了MySQL的组件残留后继续往下面操作

安装基本依赖包组建:

1
2
3
[root@localhost imuser]# yum update -y
[root@localhost imuser]# yum install libaio-devel.x86_64
[root@localhost imuser]# yum install numactl.x86_64

先准备二进制通用包如下:

1
2
[root@localhost imuser]# ls
mysql-5.6.41-linux-glibc2.12-x86_64.tar.gz

解压

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[root@localhost imuser]# tar -zxvf mysql-5.6.41-linux-glibc2.12-x86_64.tar.gz
[root@localhost imuser]# ls
mysql-5.6.41-linux-glibc2.12-x86_64 mysql-5.6.41-linux-glibc2.12-x86_64.tar.gz
#移动到安装目录下(可以自己看情况决定)
[root@localhost imuser]# mv mysql-5.6.41-linux-glibc2.12-x86_64 /usr/local/mysql56
#进入安装目录下
[root@localhost imuser]# cd /usr/local/
[root@localhost local]# ls
bin etc games include lib lib64 libexec mysql56 sbin share src
[root@localhost local]# cd mysql56/
[root@localhost mysql56]# ls
bin COPYING data docs include lib man mysql-test README scripts share sql-bench support-files
[root@localhost mysql56]# cd scripts/
# scripts下面mysql_install_db就是安装的执行文件
[root@localhost scripts]# ls
mysql_install_db

安装

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
# 创建mysql运行的用户mysql和用户组mysql
[root@localhost scripts]# groupadd mysql
[root@localhost scripts]# useradd -r -g mysql mysql
# 将安装目录的所有权分配给mysql用户和mysql用户组
[root@localhost scripts]# chown -R mysql:mysql /usr/local/mysql56/
# 正式安装 指定安装的二进制文件所在路径为--basedir=/usr/local/mysql56,数据库文件路径为--datadir=/usr/local/mysql56/data,mysql执行用户为mysql
[root@localhost scripts]# ./mysql_install_db --user=mysql --basedir=/usr/local/mysql56 --datadir=/usr/local/mysql56/data


# 安装完成,截取的安装日志最后一段如下:
2018-08-22 14:38:54 2466 [Note] InnoDB: FTS optimize thread exiting.
2018-08-22 14:38:54 2466 [Note] InnoDB: Starting shutdown...
2018-08-22 14:38:55 2466 [Note] InnoDB: Shutdown completed; log sequence number 1625987
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/local/mysql56/bin/mysqladmin -u root password 'new-password'
/usr/local/mysql56/bin/mysqladmin -u root -h localhost.localdomain password 'new-password'

Alternatively you can run:

/usr/local/mysql56/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:

cd . ; /usr/local/mysql56/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

cd mysql-test ; perl mysql-test-run.pl

Please report any problems at http://bugs.mysql.com/

The latest information about MySQL is available on the web at

http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

WARNING: Found existing config file /usr/local/mysql56/my.cnf on the system.
Because this file might be in use, it was not replaced,
but was used in bootstrap (unless you used --defaults-file)
and when you later start the server.
The new default config file was created as /usr/local/mysql56/my-new.cnf,
please compare it with your file and take the changes you need.

我们先简单的分析下日志最后给我们的提示:

这里告诉我们一定要在mysql启动后设置root密码。有两种方式: /usr/local/mysql56/bin/mysqladmin -u root password 'new-password'/usr/local/mysql56/bin/mysqladmin -u root -h localhost.localdomain password 'new-password'/usr/local/mysql56/bin/mysql_secure_installation

1
2
3
4
5
6
7
8
9
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/local/mysql56/bin/mysqladmin -u root password 'new-password'
/usr/local/mysql56/bin/mysqladmin -u root -h localhost.localdomain password 'new-password'

Alternatively you can run:

/usr/local/mysql56/bin/mysql_secure_installation

这里教我们怎么启动mysql并测试mysql是否正常

1
2
3
4
5
6
7
You can start the MySQL daemon with:

cd . ; /usr/local/mysql56/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

cd mysql-test ; perl mysql-test-run.pl

这里告诉我们怎么设置Mysql为服务

1
2
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

这里基本不要管,Mysql默认的配置文件是/usr/local/mysql56/my.cnf,我这里是先前安装mysql了一次再次安装已经存在这个文件了才报的警告,正常不会有这个。

1
2
3
4
5
6
WARNING: Found existing config file /usr/local/mysql56/my.cnf on the system.
Because this file might be in use, it was not replaced,
but was used in bootstrap (unless you used --defaults-file)
and when you later start the server.
The new default config file was created as /usr/local/mysql56/my-new.cnf,
please compare it with your file and take the changes you need.

好了分析完了,我们启动mysql,将mysql做出系统服务并设置开机启动

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
# 按mysql的提示运行脚本启动
[root@localhost scripts]# /usr/local/mysql56/bin/mysqld_safe &
[1] 2506
[root@localhost scripts]# Logging to '/usr/local/mysql56/data/localhost.localdomain.err'.
180822 14:51:58 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql56/data
# 已启动,运行用户为mysql
[root@localhost scripts]# ps -ef | grep mysql
root 2506 2313 0 14:51 pts/0 00:00:00 /bin/sh /usr/local/mysql56/bin/mysqld_safe
mysql 2575 2506 0 14:51 pts/0 00:00:00 /usr/local/mysql56/bin/mysqld --basedir=/usr/local/mysql56 --datadir=/usr/local/mysql56/data --plugin-dir=/usr/local/mysql56/lib/plugin --user=mysql --log-error=localhost.localdomain.err --pid-file=localhost.localdomain.pid
root 2598 2313 0 14:52 pts/0 00:00:00 grep mysql
# 建立mysql命令的软连接
[root@localhost scripts]# ln -s /usr/local/mysql56/bin/mysql /usr/bin/
[root@localhost scripts]# ln -s /usr/local/mysql56/bin/mysql /usr/bin/
[root@localhost scripts]#
# 安装初始化mysql
[root@localhost scripts]# /usr/local/mysql56/bin/mysql_secure_installation



NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!


By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
... Success!

Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
... Success!

By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
... Success!




All done! If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!


Cleaning up...

设置mysqld服务器和开机自启动

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
# 按照MySql提示设置服务
[root@localhost scripts]# cp ../support-files/mysql.server /etc/init.d/mysqld
# 修改文件
[root@localhost scripts]# vim /etc/init.d/mysqld

# If you change base dir, you must also change datadir. These may get
# overwritten by settings in the MySQL configuration files.

#文件里面这两项要填对
basedir=/usr/local/mysql56
datadir=/usr/local/mysql56/data


# 修改完了我们验证服务是否可用,如下说明可用
[root@localhost scripts]#
[root@localhost scripts]# service mysqld restart
Shutting down MySQL..180822 15:02:21 mysqld_safe mysqld from pid file /usr/local/mysql56/data/localhost.localdomain.pid ended
SUCCESS!
Starting MySQL. SUCCESS!
[1]+ Done /usr/local/mysql56/bin/mysqld_safe、


# 设置开机启动

[root@localhost scripts]# chkconfig --add mysqld
[root@localhost scripts]# chkconfig mysqld on

#我们重启系统看下
[root@localhost scripts]# reboot
#重新登陆后

The system is going down for reboot NOW!
[root@localhost scripts]#
Channel(Socket) closed from remote host(CentOs02) at 15:10:31.

Type `help' to learn how to use Xshell prompt.
[C:\~]$

Connecting to 192.168.56.104:22...
Connection established.
To escape to local shell, press 'Ctrl+Alt+]'.

WARNING! The remote SSH server rejected X11 forwarding request.
Last login: Wed Aug 22 14:37:46 2018 from 192.168.56.102
[imuser@localhost ~]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.41 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

到这里Linux的二进制通用安装就算完成了
link

window安装

windows可执行文件安装很简单,完全图形化没有难度。二进制解压版与linux二进制通用安装版差不多,具体的百度。

Linux下配置

接上面的Linux二进制安装版内容。我们对mysql进行细节配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
# mysql提示过我们配置文件的所在位置。我们接下来就在这个文件上修改。初始内容如下
[root@localhost mysql56]# vim my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

可以按如下参考配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.

# 日志文件设置(选填)
log_bin=1
log-error=/usr/local/mysql56/logs/error.log
general_log=1
general_log_file=/usr/local/mysql56/logs/mysql.log
slow_query_log=1
long_query_time=1
slow-query-log-file= /usr/local/mysql56/logs/slowquery.log
# These are commonly set, remove the # and set as required.

# 数据库设置(必填)
basedir = /usr/local/mysql56
datadir = /usr/local/mysql56/data
port = 3306
server_id = 1001
socket = /tmp/mysql.sock
character-set-server = utf8
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

配置后执行:service mysqld restart重启mysql使服务生效

设置用户和密码并赋予远程访问

例子:

创建用户tcl_test_user,密码为tcl123可以远程访问数据库tcl_test并拥有所有的tcl_test权限

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
[root@localhost mysql56]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.41-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database tcl_test;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE USER 'tcl_test_user'@'%' IDENTIFIED BY 'tcl123';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON tcl_test.* TO 'tcl_test_user'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

防火墙放行

1
2
3
[root@localhost mysql56]# /sbin/iptables -I INPUT -p tcp --dport 3306 -j ACCEPT
[root@localhost mysql56]# /etc/init.d/iptables save
iptables: Saving firewall rules to /etc/sysconfig/iptables:[ OK ]

在其他linux主机上尝试远程登陆

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
[root@localhost mysql56]# mysql -h 192.168.56.103 -P 3306 -u tcl_test_user -p
Enter password:
ERROR 1045 (28000): Access denied for user 'tcl_test_user'@'192.168.56.104' (using password: YES)
[root@localhost mysql56]# mysql -h 192.168.56.103 -P 3306 -u tcl_test_user -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.41-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

如上证明设置成功

设置主从

接这讲主从配置,前面我们讲的都是单机般的安装。这里我在虚拟机上开了2个完全一样的Linux6.9/MySQL5.6虚拟机,并设置他们在同一个虚拟局域网里面(两台主机可以相互ping通对方的ip,可以相互远程登陆对方的MySQL)

我们以最基本的一主一从双机主从为例子演示。

原理:

MySQL主从原理

  1. 每个从仅可以设置一个主,一个主有多个从,一个主也可以是从。
  2. 主在执行 SQL 之后,记录二进制 LOG 文件(bin-log)。
  3. 从连接主,并从主获取 binlog,存于本地 relay-log,并从上次记住的位置起执行 SQL,一旦遇到错误则停止同步以保证从机数据不受破坏,知道人为解决这些问题才可以继续。

Replication原理推论

  1. 主从间的数据库不是实时同步,就算网络连接正常,也存在瞬间,主从数据不一致。
  2. 如果主从的网络断开,从会在网络正常后,批量同步。
  3. 如果对从进行修改数据,很可能从在执行主的bin-log出错而停止同步,一般不会修改从的数据。
  4. 一个衍生的配置是双主,互为主从配置,只要双方的修改不冲突,可以工作良好。
  5. 如果需要多主的话,可以用环形配置,这样任意一个节点的修改都可以同步到所有节点。
  6. 可以应用在读写分离的场景中,用以降低单台 MySQL 服务器的 I/O。
  7. 可以实现 MySQL 服务的 HA 集群。
  8. 可以是一主多从,也可以是相互主从(主主)。

硬件配置

我们在虚拟机上搭建了两个相同配置的虚拟机,步骤是上文的单机安装方式。设置主从要保证两个虚拟机间网络相互连通,这是前提。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 主库所在主机的ip
192.168.56.103
# 从库所在主机的ip
192.168.56.104


#可以看到它们在一个局域网内,互相可以ping通

#主ping从
[root@localhost data]# ping 192.168.56.104
PING 192.168.56.104 (192.168.56.104) 56(84) bytes of data.
64 bytes from 192.168.56.104: icmp_seq=1 ttl=64 time=0.231 ms

#从ping主
[root@localhost data]# ping 192.168.56.103
PING 192.168.56.103 (192.168.56.103) 56(84) bytes of data.
64 bytes from 192.168.56.103: icmp_seq=1 ttl=64 time=0.210 ms
64 bytes from 192.168.56.103: icmp_seq=2 ttl=64 time=0.667 ms

配置主机

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
# 修改my.cnf如下

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
bind-address= 0.0.0.0
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.

# 这里和主从相关,我们打开log_bin并设置文件名为master_log_bin
log_bin=master_log_bin


log-error=/usr/local/mysql56/logs/error.log
general_log=1
general_log_file=/usr/local/mysql56/logs/mysql.log
slow_query_log=1
long_query_time=1
slow-query-log-file= /usr/local/mysql56/logs/slowquery.log
# These are commonly set, remove the # and set as required.
basedir = /usr/local/mysql56
datadir = /usr/local/mysql56/data
port = 3306

# 这里和主从相关,我们设置主机ID为1001,这个是唯一的,与其他mysql主机一定要不同
server_id = 1001
socket = /tmp/mysql.sock
character-set-server = utf8
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

修改后重启就可以了,我们看到数据库data目录下出现master_log_bin.000001,master_log_bin.index文件,这是log_bin=master_log_bin设置的效果,说明mysql主机开始准备同步文件了。

1
2
3
4
5
[root@localhost data]# service mysqld start
Starting MySQL. SUCCESS!
[root@localhost data]# ls
auto.cnf ib_logfile0 localhost.localdomain.pid master_log_bin.index performance_schema
ibdata1 ib_logfile1 master_log_bin.000001 mysql

总结下就是:

  1. log_bin=master_log_bin 这个数据输出文件要设置,文件名自己取就行
  2. server_id = 1001 这个MySQL机器的ID要设置,id自己取就行

主机上创建主从复制账号给从机

为了让 slave 能够通过 master 来获取二进制日志,需要专门给 slave 创建一个用户 slave_bak,在主机上操作。

1
grant replication slave on *.* to 'slave_bak'@'192.168.56.104' identified by 'tcl123';

我们看当前主机备份状态。执行完之后记录下这两值,然后在配置完从服务器之前不要对主服务器进行任何操作(最好临时冻结主机操作),因为每次操作数据库时这两值会发生改变。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

# 记住master_log_bin.000001备份文件和它的位置120,从机将从这里开始同步
mysql> show master status;
+-----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+-------------------+
| master_log_bin.000001 | 120 | | | |
+-----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# 当前我们没有建库建表。为了演示以前的库,表已经清除
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

配置从机

从机主动连接主机,并指定备份文件和它的位置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> change master to master_host="192.168.56.103",master_user="slave_bak",master_password="tcl123",master_log_file="master_log_bin.000001",master_log_pos=120;

# OK说明设置成功
Query OK, 0 rows affected, 2 warnings (0.04 sec)

# 从机目前是干净的,与主机一致
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

验证主从功能

主机

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
mysql> create database tcl_test;
Query OK, 1 row affected (0.00 sec)
mysql> use tcl_test;
Database changed

# 建表
mysql> CREATE TABLE test1
-> (
-> id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
-> name varchar(16) NOT NULL
-> );
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE UNIQUE INDEX test1_id_uindex ON test1 (id);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> CREATE UNIQUE INDEX test1_name_uindex ON test1 (name);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

....

#建表语句
CREATE TABLE test1
(
id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
name varchar(16) NOT NULL
);
CREATE UNIQUE INDEX test1_id_uindex ON test1 (id);
CREATE UNIQUE INDEX test1_name_uindex ON test1 (name);
....

mysql> INSERT INTO `tcl_test`.`test1` (`name`) VALUES ('tcl.co')
mysql> INSERT INTO `tcl_test`.`test1` (`name`) VALUES ('电子');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;
+----+--------+
| id | name |
+----+--------+
| 1 | tcl.co |
| 2 | 电子 |
+----+--------+
2 rows in set (0.00 sec)

从机

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
# 开启同步(这一步其实是在从机配置完与主机的主从就立即要打开的,我演示时忘了,好在从机设置的位置在建表建库插入数据的前面,就依然拉取到了主机的操作,导致同步成功)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

# 验证是否与主机一致
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| tcl_test |
+--------------------+
4 rows in set (0.00 sec)

mysql> use tcl_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from test1;
+----+--------+
| id | name |
+----+--------+
| 1 | tcl.co |
| 2 | 电子 |
+----+--------+
2 rows in set (0.00 sec)

主机从机的数据文件记录

从机data里面的relay-log.info就是主机同步过来的

1
2
3
4
5
6
[root@localhost mysql56]# cd data/
[root@localhost data]# ls
auto.cnf localhost.localdomain.err localhost-relay-bin.index relay-log.info
ibdata1 localhost.localdomain.pid master.info tcl_test
ib_logfile0 localhost-relay-bin.000001 mysql
ib_logfile1 localhost-relay-bin.000002 performance_schema

主机的master_log_bin.000001是记录自己要同步的数据

1
2
3
[root@localhost data]# ls
auto.cnf ib_logfile0 localhost.localdomain.pid master_log_bin.index performance_schema
ibdata1 ib_logfile1 master_log_bin.000001 mysql tcl_test

注意:master_log_bin.000001后面的数字在mysql每次重启后都会自动加1,可能导致从机前面的配置文件名和主机当前的备份文件名版本不一样,具体的还在研究。

网上有人问

问:master服务器重启后,就要在slave服务器上配置 change,修改master_log_file为新的值???

答:master 重启后,会重新生成bing-log文件,效果相当于flush logs,master 的binlog名增加1,此时slave不需要更改 master_log_file。 如果master执行了reset master, slave需要重置

我特意验证了下:回答是对的

主机重启插入了一条新数据,mysql文件出现了master_log_bin.000002,在master_log_bin.000001上面加了1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
[root@localhost data]# service mysqld restart
Shutting down MySQL............ SUCCESS!
Starting MySQL. SUCCESS!
[root@localhost data]# ls
auto.cnf ib_logfile0 localhost.localdomain.pid master_log_bin.000002 mysql tcl_test
ibdata1 ib_logfile1 master_log_bin.000001 master_log_bin.index performance_schema
[root@localhost data]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.41-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use tcl_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from test1;
+----+--------+
| id | name |
+----+--------+
| 1 | tcl.co |
| 3 | 家电 |
| 2 | 电子 |
+----+--------+
3 rows in set (0.00 sec)

从机不做任何改动,依然同步成功

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
[root@localhost data]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.6.41 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use tcl_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from test1;
+----+--------+
| id | name |
+----+--------+
| 1 | tcl.co |
| 3 | 家电 |
| 2 | 电子 |
+----+--------+
3 rows in set (0.00 sec)

MySQL主从复制的原理和实践操作

主从同步的误解

  • 主机的数据库,表不能同步,同步的是一条条数据
    • 错,主从可以同步库,表和数据
    • 为什么不能同步因为主从在同步前就压根结构不一样
      • 例如 master 1个月前建了库A表A.a1这时假设备份文件位置在POS0001上,一个月后各种对master数据库的操作导致备份文件的位置在POS0007上,你的从机这时候设置从POS0007开始同步,那么丢失了以前的建表建库操作,当然就同步不了,此后的所有对A库的操作从库都会报A库没有的错误。
      • 解决方案是 1. 先冻结下主机,同步主机的数据到从机后,是主从文件和结构配对了,再设置同步位置为POS0007就可以了,这时从POS0007开始同步两机是可以的。2. 如果知道主从不一致是在POS0001开始的,此后从机数据落后主机 ,那么设置POS0001开始同步也能解决