阿里免费ecs mysql配置_阿里云ECS配置之MYSQL

下载

wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.33-linux-glibc2.5-x86_64.tar.gz

安装

解压

tar -xvzf mysql-5.6.33-linux-glibc2.5-x86_64.tar.gz

重命名文件夹，把解压后的文件COPY到/usr/local/mysql目录

cp -r mysql-5.6.33-linux-glibc2.5-x86_64 /usr/local/mysql

添加mysql用户组以及用户

groupadd mysql

useradd -r -g mysql mysql

创建mysql数据目录，新目录不存在则创建

数据库数据默认目录datadir=/var/lib/mysql，可通过vim /etc/my.cnf 查看

cd /usr/local/mysql/

mkdir -p ./data/mysql

修改目录权限

chown -R mysql:mysql ./

初始化数据库

./scripts/mysql_install_db –user=mysql –datadir=/usr/local/mysql/data/mysql

image.png

修改端口

vim /etc/my.cnf

[mysqld]

port=3506

重启mysql

service mysqld restart

再次查询mysql 端口，端口已发生改变

image.png

需要开放远程登陆权限，解决方案如下

grant all privileges on *.* to ‘root’@’%’ identified by ‘123456’ with grant option;

新建用户以及数据库并授权

一般情况下，root用户不对外开放，所以需要新建用户以及数据库并进行授权

root登陆mysql创建数据库

create database db_egold;

授权db_egold数据库给新用户

grant all privileges on `db_egold`.* to ‘egoldtest’@’%’ identified by ‘yourpasswd’;

flush privileges;

查看用户，新建的用户已创建，即可以通过远程登陆访问

image.png

忘记密码

1．修改MySQL的登录设置：

vim /etc/my.cnf

在[mysqld]的段中加上一句：skip-grant-tables

例如：

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

skip-grant-tables

保存并且退出

2、重新启动mysql

service mysqld restart

3、登录并修改登录密码

#mysql

#use mysql

update user set Password=password(‘密码’) where user=’root’ ;

quit;

4、强配置改回来

去掉 skip-grant-tables

5、重新启动mysql

service mysqld restart

6、设置默认字符集

[client]

default_character_set=utf8mb4

[mysqld]

port=13306

# 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 = …..

basedir=/usr/local/mysql/

datadir=/usr/local/mysql/data/mysql

character_set_server=utf8mb4

collation_server=utf8mb4_unicode_ci

init_connect=’SET NAMES utf8mb4′

skip_character_set_client_handshake=true

lower_case_table_names=1

# 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

[mysql]

default_character_set=utf8mb4

生成删除语句

SELECT distinct CONCAT(‘delete from ‘,table_name, ‘ where 1=1;’) FROM information_schema.tables WHERE table_type=’base table’ and TABLE_NAME LIKE ‘shop_%’;

备份数据库

mysqldump -hlocalhost -P3306 -uroot -p123456 db_cinema> db_cinema_20200923.sql;

安装后，root密码是空的，需要初始化

···

update user set password=PASSWORD(‘fads’) where User=’root’;

···

文章知识点与官方知识档案匹配，可进一步学习相关知识云原生入门技能树云原生环境小结云原生的分层8755 人正在系统学习中 相关资源：凯利公司 KDZ 系列有刷串励、永磁、他励电机控制器设置软件.rar