MySQL Community Server 5.7.18 deb安装以及实现主从复制
来源：SegmentFault
时间：2023-01-19 15:49:12 425浏览 收藏
对于一个数据库开发者来说，牢固扎实的基础是十分重要的，golang学习网就来带大家一点点的掌握基础知识点。今天本篇文章带大家了解《MySQL Community Server 5.7.18 deb安装以及实现主从复制》，主要介绍了MySQL、Linux，希望对大家的知识积累有所帮助，快点收藏起来吧，否则需要时就找不到了！
1.从oracle官网下载mysql安装包。
mysql-server_5.7.18-1ubuntu16.04_amd64.deb-bundle.tar
2.解压缩安装包。
tar -xf mysql-server_5.7.18-1ubuntu16.04_amd64.deb-bundle.tar
3.使用root用户执行命令安装。
dpkg -i mysql-common_5.7.18-1ubuntu16.04_amd64.deb mysql-community-client_5.7.18-1ubuntu16.04_amd64.deb libmysqlclient20_5.7.18-1ubuntu16.04_amd64.deb libmysqlclient-dev_5.7.18-1ubuntu16.04_amd64.deb libmysqld-dev_5.7.18-1ubuntu16.04_amd64.deb mysql-client_5.7.18-1ubuntu16.04_amd64.deb mysql-community-server_5.7.18-1ubuntu16.04_amd64.deb
4.报错如下。
root@ubuntu:~$ dpkg -i mysql-common_5.7.18-1ubuntu16.04_amd64.deb mysql-community-client_5.7.18-1ubuntu16.04_amd64.deb libmysqlclient20_5.7.18-1ubuntu16.04_amd64.deb libmysqlclient-dev_5.7.18-1ubuntu16.04_amd64.deb libmysqld-dev_5.7.18-1ubuntu16.04_amd64.deb mysql-client_5.7.18-1ubuntu16.04_amd64.deb mysql-community-server_5.7.18-1ubuntu16.04_amd64.deb Selecting previously unselected package mysql-common. (Reading database ... 207638 files and directories currently installed.) Preparing to unpack mysql-common_5.7.18-1ubuntu16.04_amd64.deb ... Unpacking mysql-common (5.7.18-1ubuntu16.04) ... Selecting previously unselected package mysql-community-client. Preparing to unpack mysql-community-client_5.7.18-1ubuntu16.04_amd64.deb ... Unpacking mysql-community-client (5.7.18-1ubuntu16.04) ... Selecting previously unselected package libmysqlclient20:amd64. Preparing to unpack libmysqlclient20_5.7.18-1ubuntu16.04_amd64.deb ... Unpacking libmysqlclient20:amd64 (5.7.18-1ubuntu16.04) ... Selecting previously unselected package libmysqlclient-dev. Preparing to unpack libmysqlclient-dev_5.7.18-1ubuntu16.04_amd64.deb ... Unpacking libmysqlclient-dev (5.7.18-1ubuntu16.04) ... Selecting previously unselected package libmysqld-dev. Preparing to unpack libmysqld-dev_5.7.18-1ubuntu16.04_amd64.deb ... Unpacking libmysqld-dev (5.7.18-1ubuntu16.04) ... Selecting previously unselected package mysql-client. Preparing to unpack mysql-client_5.7.18-1ubuntu16.04_amd64.deb ... Unpacking mysql-client (5.7.18-1ubuntu16.04) ... Selecting previously unselected package mysql-community-server. Preparing to unpack mysql-community-server_5.7.18-1ubuntu16.04_amd64.deb ... Unpacking mysql-community-server (5.7.18-1ubuntu16.04) ... Setting up mysql-common (5.7.18-1ubuntu16.04) ... update-alternatives: using /etc/mysql/my.cnf.fallback to provide /etc/mysql/my.cnf (my.cnf) in auto mode dpkg: dependency problems prevent configuration of mysql-community-client: mysql-community-client depends on libaio1 (>= 0.3.93); however: Package libaio1 is not installed. dpkg: error processing package mysql-community-client (--install): dependency problems - leaving unconfigured Setting up libmysqlclient20:amd64 (5.7.18-1ubuntu16.04) ... Setting up libmysqlclient-dev (5.7.18-1ubuntu16.04) ... Setting up libmysqld-dev (5.7.18-1ubuntu16.04) ... dpkg: dependency problems prevent configuration of mysql-client: mysql-client depends on mysql-community-client (= 5.7.18-1ubuntu16.04); however: Package mysql-community-client is not configured yet. dpkg: error processing package mysql-client (--install): dependency problems - leaving unconfigured dpkg: dependency problems prevent configuration of mysql-community-server: mysql-community-server depends on mysql-client (= 5.7.18-1ubuntu16.04); however: Package mysql-client is not configured yet. mysql-community-server depends on libaio1 (>= 0.3.93); however: Package libaio1 is not installed. mysql-community-server depends on libmecab2 (>= 0.996-1.2ubuntu1); however: Package libmecab2 is not installed. dpkg: error processing package mysql-community-server (--install): dependency problems - leaving unconfigured Processing triggers for man-db (2.7.5-1) ... Processing triggers for libc-bin (2.23-0ubuntu7) ... Processing triggers for systemd (229-4ubuntu10) ... Processing triggers for ureadahead (0.100.0-19) ... Errors were encountered while processing: mysql-community-client mysql-client mysql-community-server
5.执行依赖安装，依然报错如下。
root@ubuntu:~# apt-get install libaio1 libmecab2 Reading package lists... Done Building dependency tree Reading state information... Done You might want to run 'apt-get -f install' to correct these: The following packages have unmet dependencies: openssh-server : Depends: openssh-client (= 1:7.2p2-4ubuntu2.2) openssh-sftp-server : Depends: openssh-client (= 1:7.2p2-4ubuntu2.2) E: Unmet dependencies. Try 'apt-get -f install' with no packages (or specify a solution).
6.执行命令安依赖包。
root@ubuntu:~# apt-get install libaio1 libmecab2 openssh-client Reading package lists... Done Building dependency tree Reading state information... Done Suggested packages: ssh-askpass libpam-ssh keychain monkeysphere The following NEW packages will be installed: libaio1 libmecab2 The following packages will be upgraded: openssh-client 1 upgraded, 2 newly installed, 0 to remove and 316 not upgraded. 3 not fully installed or removed. Need to get 271 kB/857 kB of archives. After this operation, 1,958 kB of additional disk space will be used. Get:1 http://us.archive.ubuntu.com/ubuntu/ xenial/main amd64 libaio1 amd64 0.3.110-2 [6,356 B] Get:2 http://us.archive.ubuntu.com/ubuntu/ xenial/universe amd64 libmecab2 amd64 0.996-1.2ubuntu1 [264 kB] Fetched 271 kB in 3s (79.4 kB/s) Selecting previously unselected package libaio1:amd64. (Reading database ... 208013 files and directories currently installed.) Preparing to unpack .../libaio1_0.3.110-2_amd64.deb ... Unpacking libaio1:amd64 (0.3.110-2) ... Selecting previously unselected package libmecab2. Preparing to unpack .../libmecab2_0.996-1.2ubuntu1_amd64.deb ... Unpacking libmecab2 (0.996-1.2ubuntu1) ... Preparing to unpack .../openssh-client_1%3a7.2p2-4ubuntu2.2_amd64.deb ... Unpacking openssh-client (1:7.2p2-4ubuntu2.2) over (1:7.2p2-4ubuntu2.1) ... Processing triggers for libc-bin (2.23-0ubuntu7) ... Processing triggers for man-db (2.7.5-1) ... Setting up libaio1:amd64 (0.3.110-2) ... Setting up mysql-community-client (5.7.18-1ubuntu16.04) ... Setting up mysql-client (5.7.18-1ubuntu16.04) ... Setting up libmecab2 (0.996-1.2ubuntu1) ... Setting up mysql-community-server (5.7.18-1ubuntu16.04) ... Package configuration
7.要求输入root用户密码，输入以后mysql安装成功。
update-alternatives: using /etc/mysql/mysql.cnf to provide /etc/mysql/my.cnf (my.cnf) in auto mode Setting up openssh-client (1:7.2p2-4ubuntu2.2) ... Processing triggers for libc-bin (2.23-0ubuntu7) ... Processing triggers for systemd (229-4ubuntu10) ... Processing triggers for ureadahead (0.100.0-19) ...
8.执行mysql免密码登陆报错。
root@ubuntu:~# mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
9.进入/etc/mysql目录，编辑mysqld.cnf文件。在文件最后一行加入配置“skip-grant-tables”，执行命令“service mysql restart”重启mysql。
root@ubuntu:/etc/mysql/mysql.conf.d# vim mysqld.cnf # Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved. # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation; version 2 of the License. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software # Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA # # The MySQL Server configuration file. # # For explanations see # https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html [mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql log-error = /var/log/mysql/error.log # By default we only accept connections from localhost bind-address = 127.0.0.1 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 skip-grant-tables
10.执行mysql命令，可以正确登陆mysql。
root@ubuntu:/etc/mysql/mysql.conf.d# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.18 MySQL Community Server (GPL) Copyright (c) 2000, 2017, 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>
11.安装完成，登陆以后可以执行命令修改mysql密码。
mysql> use mysql;
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> update mysql.user set authentication_string=password('root') where user='root';
Query OK, 1 row affected, 1 warning (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> flush privileges;
Query OK, 0 rows affected (0.08 sec)
mysql> exit
12.将/etc/mysql/mysql.conf.d/mysqld.cnf最后一行删除，并执行命令“service mysql restart”重启mysql。
# Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved. # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation; version 2 of the License. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software # Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA # # The MySQL Server configuration file. # # For explanations see # https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html [mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql log-error = /var/log/mysql/error.log # By default we only accept connections from localhost bind-address = 127.0.0.1 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0
13.输入命令“mysql -u root -p”，输入密码“root”，登陆成功。
root@ubuntu:/etc/mysql/mysql.conf.d# 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.7.18 MySQL Community Server (GPL) Copyright (c) 2000, 2017, 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>
14.至此，MySQL Community Server 5.7.18 deb安装成功。
15.在两台服务器上分别安装MySQL Community Server 5.7.18 deb版本，修改主库配置文件
/etc/mysql/mysql.conf.d/mysqld.cnf,增加最后两行配置。
[mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql log-error = /var/log/mysql/error.log # By default we only accept connections from localhost #bind-address = 127.0.0.1 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 server-id=107 log-bin=/var/lib/mysql/mysql-bin
16.从库也需要修改配置文件/etc/mysql/mysql.conf.d/mysqld.cnf，同样增加最后两行，注意server-id要不同。
[mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql log-error = /var/log/mysql/error.log # By default we only accept connections from localhost #bind-address = 127.0.0.1 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-bin=/var/lib/mysql/mysql-bin server-id=109
17.重启两台服务器mysql服务，service mysql restart。
18.主库建立账户并授予slave权限
mysql> GRANT REPLICATION SLAVE ON *.* to 'test'@'%' identified by 'test'; Query OK, 0 rows affected, 1 warning (0.01 sec)
19.查询主库状态
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000004 | 1343 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
20.配置从库，停止从库复制功能；配置与主库同步信息，master_host为主库ip地址，master_user，master_password为18步创建的用户名和密码，master_log_file和master_log_pos为19步查询出的File和Position。 配置完成后，启动从库复制功能。
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> change master to
-> master_host='192.168.244.128',
-> master_user='test',
-> master_password='test',
-> master_log_file='mysql-bin.000004',
-> master_log_pos=1343;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
21.检查下从库的配置信息，注意Slave_IO_Running: Yes和Slave_SQL_Running: Yes两个配置项必须为Yes，否则存在问题。需要重新执行19,20步进行配置。
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.244.128
Master_User: test
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 1343
Relay_Log_File: ubuntu-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000004
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: 1343
Relay_Log_Space: 528
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: 107
Master_UUID: 2073267a-44e9-11e7-a5ce-000c29c1f067
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
22.配置完成，进行下测试。在主库创建database和table。
mysql> create database db_test;
Query OK, 1 row affected (0.01 sec)
mysql> use db_test;
Database changed
mysql> create table db_table(name char(13),age int(3));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into db_table values('tom', 19);
Query OK, 1 row affected (0.01 sec)
23.查询下主库信息。
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db_test | | mysql | | performance_schema | | sys | +--------------------+ 8 rows in set (0.00 sec) mysql> select * from db_table; +------+------+ | name | age | +------+------+ | tom | 19 | +------+------+ 1 row in set (0.00 sec)
24.查询下从库信息。发现从库中已经从主库中同步了db_test database以及db_table表数据。
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db_test | | mysql | | performance_schema | | sys | +--------------------+ 7 rows in set (0.00 sec) mysql> use db_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 db_table; +------+------+ | name | age | +------+------+ | tom | 19 | +------+------+ 1 row in set (0.00 sec)
25.至此，Mysql完成了主从数据库同步配置和数据同步。
26.操作过程中遇到一个问题。在配置从库以后，出现Slave_IO_RunningConnecting和Slave_SQL_Running: Yes状态，查看下面错误信息，发现报错无法连接到主库ip，手动连接了一下报错“ERROR 2003 (HY000): Can't connect to MySQL server on 'xxx.xxx.xxx.xxx'(111)错误”，无法连接到主库。
解决过程：将主库/etc/mysql/mysql.conf.d/mysqld.cnf配置文件中bind-address= 127.0.0.1注销掉，重启主库即可，不要绑定本地回环地址。
[mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql log-error = /var/log/mysql/error.log # By default we only accept connections from localhost #bind-address = 127.0.0.1 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 server-id=107 log-bin=/var/lib/mysql/mysql-bin
今天关于《MySQL Community Server 5.7.18 deb安装以及实现主从复制》的内容介绍就到此结束，如果有什么疑问或者建议，可以在golang学习网公众号下多多回复交流；文中若有不正之处，也希望回复留言以告知！
声明：本文转载于：SegmentFault 如有侵犯，请联系study_golang@163.com删除
相关阅读
更多>
-
499 收藏
-
244 收藏
-
235 收藏
-
157 收藏
-
101 收藏
最新阅读
更多>
-
208 收藏
-
174 收藏
-
317 收藏
-
371 收藏
-
244 收藏
-
288 收藏
课程推荐
更多>
-
- 前端进阶之JavaScript设计模式
- 设计模式是开发人员在软件开发过程中面临一般问题时的解决方案，代表了最佳的实践。本课程的主打内容包括JS常见设计模式以及具体应用场景，打造一站式知识长龙服务，适合有JS基础的同学学习。
- 立即学习 542次学习
-
- GO语言核心编程课程
- 本课程采用真实案例，全面具体可落地，从理论到实践，一步一步将GO核心编程技术、编程思想、底层实现融会贯通，使学习者贴近时代脉搏，做IT互联网时代的弄潮儿。
- 立即学习 507次学习
-
- 简单聊聊mysql8与网络通信
- 如有问题加微信：Le-studyg；在课程中，我们将首先介绍MySQL8的新特性，包括性能优化、安全增强、新数据类型等，帮助学生快速熟悉MySQL8的最新功能。接着，我们将深入解析MySQL的网络通信机制，包括协议、连接管理、数据传输等，让
- 立即学习 497次学习
-
- JavaScript正则表达式基础与实战
- 在任何一门编程语言中,正则表达式,都是一项重要的知识,它提供了高效的字符串匹配与捕获机制,可以极大的简化程序设计。
- 立即学习 487次学习
-
- 从零制作响应式网站—Grid布局
- 本系列教程将展示从零制作一个假想的网络科技公司官网，分为导航，轮播，关于我们，成功案例，服务流程，团队介绍，数据部分，公司动态，底部信息等内容区块。网站整体采用CSSGrid布局，支持响应式，有流畅过渡和展现动画。
- 立即学习 484次学习