MYSQL常用指令以及安装

Celia 字数: 6512 阅读耗时: 16 分钟 2023/06/08 2023/08/13 博客独享热度: 331 评论: 0

MYSQL

在Debian软件包中以及更名mariadb

安装直接就是

apt install mariadb-server

二进制安装

创建用户组

groupadd -r mysql && useradd -r -g mysql -s /sbin/nologin -M mysql

创建文件目录以及更改权限

mkdir -p /mysql/data && chown mysql:mysql /mysql/data
mkdir -p /mysql/log && chown mysql:mysql /mysql/log
mkdir -p /var/lib/mysql && chown mysql:mysql /var/lib/mysql

环境变量

vim /etc/profile
export MYSQL_BASE=/usr/local/mysql

export PATH=$MYSQL_BASE/bin:$PATH

my.cnf配置


[mysqld]
#basic settings
secure_file_priv =
server-id = 3306
port = 3306
user = mysql
autocommit = 1
character_set_server = utf8mb4
socket = /var/lib/mysql
skip_name_resolve = 1
max_connections = 1000
basedir = /usr/local/mysql
datadir = /mysql/data
pid-file = /run/mysql
transaction_isolation = READ-COMMITTED
join_buffer_size = 134217728
tmp_table_size = 67108864
tmpdir = /tmp
max_allowed_packet = 16777216
sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
interactive_timeout = 1800
wait_timeout = 1800
sort_buffer_size = 33554432
#log settings
log_error = error.log
slow_query_log = on
slow_query_log_file = slow.log
expire_logs_days = 7
long_query_time = 3
log_bin = bin.log
sync_binlog = 1
binlog_format = row 
#innodb settings
innodb_buffer_pool_size = 2048M
innodb_data_file_path = ibdata1:1G:autoextend
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lock_wait_timeout = 5
innodb_log_file_size = 500M
innodb_log_buffer_size = 16777216
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 67108864
innodb_flush_log_at_trx_commit = 2
log_bin_trust_function_creators=1
#set client
 
[client]
port=3306
socket=/var/lib/mysql

初始化

/usr/local/mysql/mysql/bin/mysqld --defaults-file=/usr/local/mysql/my.cnf --initialize --user=mysql

常用命令

初始化化命令

mysql_secure_installation

会有大概步骤，内容大概是

第一步确认你的root密码
第二步询问您是否要切换为使用 unix_socket 身份验证方式选择N即可
第三步是否要更改默认root密码上面改过了，选择N
第四步是否删除默认匿名账户选择Y
第五步是否运行root账户远程连接需要的话选择Y
第六步是否删除测试数据库选择Y
第七部是否刷新权限选择Y

到这基本就完成了

库内基本命令

查看所有库

SHOW DATABASES;

查看特定的库

USE database_name;
SHOW TABLES;

查看mysql中的用户以及主机权限

SELECT User, Host, authentication_string FROM mysql.user;

为 root 用户授予远程访问权限

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';

刷新权限

FLUSH PRIVILEGES;

远程连接到指定的数据库

mysql -u root -p -h server_ip_address

创建数据库

CREATE DATABASE newdatabase;

创建一个新的用户并为其分配权限

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON newdatabase.* TO 'newuser'@'localhost';

账户为newuser密码为password自行修改@后面是登陆的方式改成%允许远程改成ip地址是指定一个ip访问,下面是库名和允许登陆的用户和远程权限。

授权远程计算机访问指定的库

GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'192.168.1.2' IDENTIFIED BY 'mypassword';

指定库名连接

mysql -u myuser -p -h server_ip_address mydatabase

修改 MySQL 的配置文件 my.cnf，以允许远程连接

# bind-address = 127.0.0.1
bind-address = 0.0.0.0