返回
软件
分类

必赢备用网址先装个数据库给开发用,我使用的是普通用户进行操作

日期: 2020-04-07 06:13 浏览次数 : 199

最近一个项目要部署在阿里云上,为了开发团队方便,我自费买了个ECS,先装个数据库给开发用。

        这个系列的目的是学习使用Binlog完成数据同步,重点在Canel的部分,所以MySQL的东西以能完成任务为主,不会涉及得太深。下面是我在本地VMWare上安装MySQL5.7时的流水账。

因为之前都是在真机安装,与这次阿里云上的部署比起来,还是有点区别的。

        在默认情况下,我使用的是普通用户进行操作,下面步骤中如有需要root用户执行的操作我会特殊标记出来,操作完毕后,就会切换回普通用户,每步后都不再单独说明

 

1. 到MySQL的官方网站上下载5.7的安装包,我选择的安装包是:mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz

Mysql

  1. 下载完毕后解压此文件并复制到/usr/local/mysql目录

1 安装mysql版本包

wget    

yum install mysql57-community-release-el7-11.noarch.rpm

 

vim /etc/yum.repos.d/mysql-community.repo

 把5.6的enable置为1,5.7的置为0

 

3. 修改/usr/local/mysql目录的所有者和所有者用户组,这里我已经创建好了mysql用户和mysql用户组,直接chown -R即可

2 安装mysql:

 

yum install mysql mysql-community-server -y

 # 建立慢查询日志文件,如果没有安装时候会报错

必赢备用网址 ,touch /var/log/slow.log

#给mysql用户授权这个文件

chown mysql:mysql slow.log

 

 

-------------------------------配置文件------------------------------------------------

# For advice on how to change settings please see
# 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 = 512M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# 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
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
#symbolic-links=0
autocommit = 1

slow_query_log = on
slow_query_log_file =/var/log/slow.log
long_query_time = 5

# Recommended in standard MySQL setup
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

 -------------------------------配置文件------------------------------------------------

因为是开发环境,没有压力,所以没进行优化,实际生产时候innodb_buffer_pool_size 参数非常重要,一定要加大到内存70%-80%,之前吃过亏。

 

  1. 使用mysql用户在/usr/local/mysql目录下创建data目录

3 报错

[ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.

进入usr目录

./mysql_install_db --user=mysql

5.到/usr/local/mysql/bin 目录下使用命令初始化MySQL数据库,命令如下

4 启动命令

systemctl status mysqld.service

systemctl enable mysqld.service   开机启动

systemctl start mysqld.service 启动mysql服务

systemctl restart mysqld.service 重启mysql 服务

systemctl stop mysqld.service

 

    ./mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --initialize 

5 初次登录设置

mysql_secure_installation

  • Set root password? [Y/n] 
    是否设置root用户的密码
  • Remove anonymous users? [Y/n] 
    是否删除匿名用户
  • Disallow root login remotely? [Y/n] 
    是否禁止root远程登录
  • Remove test database and access to it? [Y/n] 
    是否删除database数据库
  • Reload privilege tables now? [Y/n] 
    是否重新加载授权信息

授权远程用户登录

GRANT ALL PRIVILEGES ON *.* TO '用户名'@'%'identified by '密码';

 但此时会因为我的操作系统版本较低缺少某些so文件,报错如下:

 

./mysqld: error while loading shared libraries: libnuma.so.1: cannot open shared object file: No such file or directory

解决的办法很简单,使用yum安装确实的工具即可(需要root用户)

yum install numactl.x86_64

然后重新执行初始化命令,得到的结果如下所示

2018-02-19T12:33:12.916561Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2018-02-19T12:33:13.202091Z 0 [Warning] InnoDB: New log files created, LSN=45790 2018-02-19T12:33:13.267153Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2018-02-19T12:33:13.381773Z 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: 0d52ca47-1571-11e8-9e71-000c2903b85a. 2018-02-19T12:33:13.383464Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2018-02-19T12:33:13.384129Z 1 [Note] A temporary password is generated for root@localhost: Xljjktiy34>a

最后的 Xljjktiy34>a 是给root用户(这个root用户是mysql的root用户,不是操作系统的root)分配的临时密码,把它记下来,后面登录需要用到它。

  1. 回到/usr/local/mysql目录下,输入如下命令启动MySQL

./support-files/mysql.server start 

我在启动的时候遇到个ERROR

Starting MySQL.2018-02-19T12:34:46.365402Z mysqld_safe Directory '/var/lib/mysql' for UNIX socket file don't exists. ERROR! The server quit without updating PID file

我们直接创建该目录并授权777,然后把/etc目录下的my.cnf删掉

这两处修改完毕之后,再启动MySQL,显示成功

Starting MySQL. SUCCESS!

  1. 进入到/usr/local/mysql/bin 目录下,使用如下命令进入MySQL

./mysql -uroot -p 

此时会提示用户输入密码,我们需要输入的是刚才初始化时给root用户分配的临时密码。

8. 因为MySQL默认不允许root用户从本地连接,我们需要修改下配置,123456是我为root用户设置的新密码

set password=password('123456');

use mysql;

GRANT ALL PRIVILEGES ON *.* TO root@'%' identified by '123456';

flush privileges;

执行完上述语句后,root用户可以在任何网络环境访问本机的MySQL数据库了。如果从其他机器上访问不成功,记得把防火墙设置下3306端口例外,懒一点的同学就直接关掉防火墙吧。

完毕。过程中剩下的问题基本都是缺少目录,手动创建然后授权就好了。

最后要说明的是,MySQL的配置文件my.cnf 可以在多个目录下存在,MySQL在不同的操作系统中有不同的优先加载策略。保证本地机器中只有一个my.cnf很重要,每次修改my.cnf后都要重启MySQL才可以生效。下面是一份基本的my.cnf内容,我因为要查看MySQL的binlog,所以需要设置log-bin参数,之前有一步我把/etc/my.cnf删掉了,导致我的本地机器中根本没有这个配置文件,也就没办法修改binlog的存储位置。

# 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

general_log_file = /var/log/mysql/mysql.log

general_log = 1

# These are commonly set, remove the # and set as required.

# basedir = ..... # datadir = ..... # port = ..... # server_id = ..... # socket = ..... #kip-external-locking

skip-name-resolve

basedir=/usr/local/mysql

datadir=/usr/local/mysql/data

pid-file=/usr/local/mysql/data/mysql.pid

port=3306

character_set_server=utf8

init_connect='SET NAMES utf8'

log-error=/usr/local/mysql/data/mysqld.err

slow_query_log = 1

slow_query_log_file =/usr/local/mysql/data/slow-query.log

long_query_time = 1

log-queries-not-using-indexes

max_connections = 1024

back_log = 128

wait_timeout = 100

interactive_timeout = 200

key_buffer_size=256M

query_cache_size = 256M

query_cache_type=1

query_cache_limit=50M

max_connect_errors=20

sort_buffer_size = 2M

max_allowed_packet=16M

join_buffer_size=2M

thread_cache_size=200

innodb_buffer_pool_size = 2048M

innodb_flush_log_at_trx_commit = 1

innodb_log_buffer_size=32M

innodb_log_file_size=128M

innodb_log_files_in_group=3

server_id=1

log-bin=/usr/local/mysql/binlog/mysql-bin

binlog_cache_size=2M

max_binlog_cache_size=8M

max_binlog_size=512M

expire_logs_days=7

read_buffer_size=1M

read_rnd_buffer_size=16M

bulk_insert_buffer_size=64M

# 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

  • 上一篇:没有了
  • 下一篇:没有了