MYSQL常用指令以及安装
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
评论
匿名评论
隐私政策
你无需删除空行,直接评论以获取最佳展示效果