mysql遠(yuǎn)程連接指定端口和ip地址
mysql -u root -P 3307 -h 101.200.152.192 -p
使用如下命令安裝MySql以及它的相關(guān)依賴項(xiàng).
sudo dnf install @mysql
安裝完成后也切，啟動(dòng)MySql服務(wù)并且讓它在Centos服務(wù)器啟動(dòng)時(shí)自動(dòng)啟動(dòng),命令如下所示.
sudo systemctl enable --now mysqld
使用如下命令檢測(cè)MySql是否在運(yùn)行.結(jié)果如下所示.
sudo systemctl status mysqld
● mysqld.service - MySQL 8.0 database server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Wed 2020-11-11 09:41:34 CST; 3 weeks 5 days ago
Main PID: 30897 (mysqld)
Status: "Server is operational"
Tasks: 44 (limit: 10979)
Memory: 403.9M
CGroup: /system.slice/mysqld.service
└─30897 /usr/libexec/mysqld --basedir=/usr
Nov 11 09:41:26 iZ2ze3u71xuet709kawlyiZ systemd[1]: Starting MySQL 8.0 database server...
Nov 11 09:41:26 iZ2ze3u71xuet709kawlyiZ mysql-prepare-db-dir[30815]: Initializing MySQL database
Nov 11 09:41:34 iZ2ze3u71xuet709kawlyiZ systemd[1]: Started MySQL 8.0 database server.
使用如下命令進(jìn)入
sudo mysql_secure_installation
會(huì)有如下順序直接,按照如下順序執(zhí)行.
選擇密碼驗(yàn)證策略等級(jí)宦焦， 我這里選擇0 （low）窝剖，回車
輸入新密碼兩次
確認(rèn)是否繼續(xù)使用提供的密碼哥蔚？輸入y 册烈，回車
移除匿名用戶倘屹？ 輸入y 候址，回車
不允許root遠(yuǎn)程登陸？ 我這里需要遠(yuǎn)程登陸族扰，所以輸入n 厌丑，回車
移除test數(shù)據(jù)庫(kù)？ 輸入y 渔呵，回車
重新載入權(quán)限表怒竿？ 輸入y ，回車
具體栗子如下所示.
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):<–初次運(yùn)行直接回車
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] <– 是否設(shè)置root用戶密碼扩氢，輸入y并回車或直接回車
New password: <– 設(shè)置root用戶的密碼
Re-enter new password: <– 再輸入一次你設(shè)置的密碼
###這里如果出現(xiàn)密碼... Failed! Error: Your password does not satisfy the current policy requirements
Password updated successfully! 則重新輸入,這時(shí)可以先設(shè)置一個(gè)MySQL安全配置向?qū)ㄟ^(guò)的密碼 比如 Root_12root 先設(shè)置成功,后續(xù)我們更改
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] <– 是否刪除匿名用戶,生產(chǎn)環(huán)境建議刪除耕驰，所以直接回車
… 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] <–是否禁止root遠(yuǎn)程登錄,根據(jù)自己的需求選擇Y/n并回車,建議禁止
… 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] <– 是否刪除test數(shù)據(jù)庫(kù),直接回車
- 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] <– 是否重新加載權(quán)限表，直接回車
… Success!
Cleaning up…
All done! If you've completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!
在當(dāng)前終端使用如下命令登陸MySql.
mysql -uroot -p <上面步驟中設(shè)置的密碼>
登陸使用如下mysql命令,設(shè)置root用戶的 host 為任意IP.
use mysql; ##切換到mysql數(shù)據(jù)庫(kù)
update user set host='%' where user='root'; ##設(shè)置可以遠(yuǎn)程訪問(wèn)
flush privileges;##配置立即生效
quit; ##退出 mysql
使用如下命令關(guān)閉3306防火墻.如果不行,請(qǐng)到服務(wù)器控制臺(tái)在安全組中設(shè)置3306端口.
sudo firewall-cmd --add-port=3306/tcp --permanent ##打開(kāi)3306端口
sudo firewall-cmd --reload ##防火墻配置立即生效
修改mysql的端口或者其他信息
修改完之后需要重啟服務(wù)器哦
5.7開(kāi)啟二進(jìn)制
#5.7以下
log_bin=ON
log_bin_basename=/var/lib/mysql/mysql-bin
log_bin_index=/var/lib/mysql/mysql-bin.index
三個(gè)參數(shù)來(lái)指定录豺，
第一個(gè)參數(shù)是打開(kāi)binlog日志
第二個(gè)參數(shù)是binlog日志的基本文件名朦肘，后面會(huì)追加標(biāo)識(shí)來(lái)表示每一個(gè)文件
第三個(gè)參數(shù)指定的是binlog文件的索引文件，這個(gè)文件管理了所有的binlog文件的目錄
image.png
8.0開(kāi)啟二進(jìn)制日志
在my.cnf主配置文件中直接添加
#8.0
log_bin=/var/run/mysqld/binlog
mysql5.X双饥，需要手動(dòng)寫log_bin=ON
mysql8.0開(kāi)始媒抠，默認(rèn)就開(kāi)戶bin_log，所以不需要再配置開(kāi)關(guān)log_bin=ON
show variables like ‘%log_bin%’;查詢得到的字段信息是log_bin_basename=xxx和log_bin_index=xxx咏花，實(shí)際上只要寫log_bin就可以了
如果需要修改bin_log日志的存儲(chǔ)位置需要先停止mysql趴生，再把原bin_log路徑的下的bin_log*全部移到新路徑，再啟動(dòng)mysql
mysql5.7以上需要添加server-id，不然啟動(dòng)報(bào)錯(cuò)冲秽，mysql5.7以下不需要添加server-id舍咖。
重新啟動(dòng)我們的mysql
啟動(dòng)成功之后，我們可以登陸查看我們的配置是否起作用
show variables like '%log_bin%'
修改文件夾的所有者
chown -R jay:fefjay my #修改my文件所屬用戶為jay锉桑，所屬用戶組為fefjay
mysql的啟動(dòng)關(guān)閉
#開(kāi)啟
systemctl start mysqld
service mysqld start
#關(guān)閉
systemctl stop mysqld
service mysqld stop
#重啟
service mysqld restart
#查看服務(wù)狀態(tài)
service mysqld status
mysql5.7以上修改密碼
- 修改 /etc/my.cnf,加入 skip-grant-tables;
vim /etc/my.cnf
- 在[mysqld]添加如下內(nèi)容排霉，沒(méi)有就自己加一個(gè)[mysqld]，并保存退出民轴；
[mysqld]
skip-name-resolve
skip-grant-tables
- 重啟服務(wù)
systemctl restart mysqld
- 空密碼直接進(jìn)入mysql
[root@centos7 ~]# 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.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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>
- 查看mysql的密碼規(guī)則
SHOW VARIABLES LIKE 'validate_password%';
- 設(shè)置密碼策略等級(jí)為
這里修改的 validate_password_policy屬性，一定要根據(jù)實(shí)際查出來(lái)的來(lái)
set global validate_password_policy=0;
#更新設(shè)置
FLUSH PRIVILEGES;
- （可選）設(shè)置密碼長(zhǎng)度
set global validate_password_length=4;
#更新設(shè)置
FLUSH PRIVILEGES;
- 修改密碼
# 切換mysql庫(kù)
use mysql
# 'root'@'localhost' 用戶名 17637945521Wzp新密碼
ALTER USER 'root'@'localhost' IDENTIFIED BY '17637945521Wzp';
#更新設(shè)置
FLUSH PRIVILEGES;
- 刪除掉/etc/my.cnf文件中之前添加的內(nèi)容 skip-grant-tables;然后重啟服務(wù)
