摘要：执行免密码登陆报错。从库也需要修改配置文件，同样增加最后两行，注意要不同。重启两台服务器服务，。检查下从库的配置信息，注意和两个配置项必须为，否则存在问题。配置完成，进行下测试。查询下从库信息。
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 # http://dev.mysql.com/doc/mysql/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 # http://dev.mysql.com/doc/mysql/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 statusG
*************************** 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
文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。
转载请注明本文地址：https://www.ucloud.cn/yun/10005.html
摘要：执行免密码登陆报错。从库也需要修改配置文件，同样增加最后两行，注意要不同。重启两台服务器服务，。检查下从库的配置信息，注意和两个配置项必须为，否则存在问题。配置完成，进行下测试。查询下从库信息。 1.从oracle官网下载mysql安装包。 mysql-server_5.7.18-1ubuntu16.04_amd64.deb-bundle.tar 2.解压缩安装包。 tar -xf ...
摘要：缺点并不是所有语句都能复制的比如，取的是数据当前时间，不同的数据可能时间不一致，另外像存储过程和触发器也可能存在问题。主从复制作用数据分布。 一，MySQL安装配置 1.下载 mysql 源安装包 $ curl -LO http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm 2.安装 mysql 源 $...
摘要：在开始之前不得不吐槽一下，网上有一些教程实在是太坑爹，教程直接复制粘贴别人的，不写环境，没有版本号说明。对于其他版本版本的环境，我没有进行测试，所以不保证能够成功，不过小版本号之间不同应该没有什么大碍。 情况说明 由于工作需要，需要在一台装有 CentOS 系统的测试服务器上安装 MySQL ，由于该服务器上存有其他比较重要的测试数据，所以不能连接外网。由于之前安装 MySQL 一直都...
摘要：在开始之前不得不吐槽一下，网上有一些教程实在是太坑爹，教程直接复制粘贴别人的，不写环境，没有版本号说明。对于其他版本版本的环境，我没有进行测试，所以不保证能够成功，不过小版本号之间不同应该没有什么大碍。 情况说明 由于工作需要，需要在一台装有 CentOS 系统的测试服务器上安装 MySQL ，由于该服务器上存有其他比较重要的测试数据，所以不能连接外网。由于之前安装 MySQL 一直都...
摘要：在开始之前不得不吐槽一下，网上有一些教程实在是太坑爹，教程直接复制粘贴别人的，不写环境，没有版本号说明。对于其他版本版本的环境，我没有进行测试，所以不保证能够成功，不过小版本号之间不同应该没有什么大碍。 情况说明 由于工作需要，需要在一台装有 CentOS 系统的测试服务器上安装 MySQL ，由于该服务器上存有其他比较重要的测试数据，所以不能连接外网。由于之前安装 MySQL 一直都...
阅读 1319·2021-11-25 09:43
阅读 3781·2021-11-22 13:53
阅读 761·2021-11-22 09:34
阅读 3447·2021-11-19 11:29
阅读 2018·2021-09-26 09:55
阅读 1012·2021-09-08 09:35
阅读 2915·2020-12-03 17:26
阅读 392·2019-08-29 16:06
