Mariadb/Mysql命令行常用命令
一、初始化等
1、登陆数据库方法
mysql -u 用户名 -p 用户密码
2、修改root及用户密码
use mysql;
update user set password=password('11111111') where user='root' and host='localhost';
flush privileges;
MariaDB [mysql]> update user set password=password('11111111') where user='root' and host='localhost'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [mysql]> flush privileges; Query OK, 0 rows affected (0.01 sec) MariaDB [mysql]> exit
3、创建用户
insert into mysql.user(host,user,password)values("localhost","test",password("password"));
flush privileges;
4、删除用户
DELETE FROM user WHERE User="test" and Host="localhost";
flush privileges;
5、删除用户的数据库
drop database test1;
6、交互模式初始化
mysql_secure_installation
二、 常用操作
1、显示数据库列表
show databases:查看所有的数据库
2、创建数据库
create database zxg：创建名尾zxg的数据库
3、进入数据库
use zxg:进入zxg的数据库
4、显示库中的数据表
show tables：查看数据库里有多少张表
5、创建数据表
create table t1 (id varchar(20),name varchar(20)):创建名为t1表，并创建两两个字段，id、name，varchar表示设置数据长度，用字符来定义长度单位，其
6、插入数据
insert into t1 values（"1"，"zxg"）:向表中插入数据
7、查看数据表
select * from t1 ：查看t1表数据内容
8、多条件查询
select * from t1 where id=1 and age = 'zxg ' : id、age 多个条件查询
9、查看字段内容
desc t1:查看t1 表字段内容
10、修改字段长度
alter table t1 modify column name varchar(20):修改name字段的长度
11、修改该字段内容
update t1 set name='zxg.net' where id=1：修改name字段的内容
12、权限刷新
flush privileges :刷新权限
13、清空表单
delete from t1 :清空表内容
14、删除数据表
drop table t1:删除表
15、删除数据库
drop database zxg：删除zxg数据库
16、查看数据库字符集
show variables like '%char%':查看数据库字符集
17、查看存储引擎
show engines:查看MySQL存储引擎。
18、查看默认存储引擎
show variables like '%storage_engine%':查看MySQL默认的存储引擎
19、修改存储引擎
alter table t1 engine=innodb:修改MySQL t1表存储引擎
1 [root@web2 ~]# mysql 2 Welcome to the MariaDB monitor. Commands end with ; or \g. 3 Your MariaDB connection id is 2 4 Server version: 5.5.60-MariaDB MariaDB Server 5 6 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. 7 8 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 9 10 MariaDB [(none)]> show databases; 11 +--------------------+ 12 | Database | 13 +--------------------+ 14 | information_schema | 15 | mysql | 16 | performance_schema | 17 | test | 18 +--------------------+ 19 4 rows in set (0.00 sec) 20 21 MariaDB [(none)]> create database zxg; 22 Query OK, 1 row affected (0.00 sec) 23 24 MariaDB [(none)]> use zxg; 25 Database changed 26 MariaDB [zxg]> show tables; 27 Empty set (0.00 sec) 28 29 MariaDB [zxg]> create table t1(id varchar(20),name varchar(20)); 30 Query OK, 0 rows affected (0.00 sec) 31 32 MariaDB [zxg]> show tables; 33 +---------------+ 34 | Tables_in_zxg | 35 +---------------+ 36 | t1 | 37 +---------------+ 38 1 row in set (0.00 sec) 39 40 MariaDB [zxg]>
1 MariaDB [zxg]> insert into t1 values ("1","zxg"); 2 Query OK, 1 row affected (0.01 sec) 3 4 MariaDB [zxg]> select *from t1; 5 +------+------+ 6 | id | name | 7 +------+------+ 8 | 1 | zxg | 9 +------+------+ 10 1 row in set (0.00 sec) 11 12 MariaDB [zxg]> select *from t1 where id=1; 13 +------+------+ 14 | id | name | 15 +------+------+ 16 | 1 | zxg | 17 +------+------+ 18 1 row in set (0.00 sec) 19 20 MariaDB [zxg]> select *from t1 where id=1 and name='zxg'; 21 +------+------+ 22 | id | name | 23 +------+------+ 24 | 1 | zxg | 25 +------+------+ 26 1 row in set (0.01 sec) 27 28 MariaDB [zxg]> desc t1; 29 +-------+-------------+------+-----+---------+-------+ 30 | Field | Type | Null | Key | Default | Extra | 31 +-------+-------------+------+-----+---------+-------+ 32 | id | varchar(20) | YES | | NULL | | 33 | name | varchar(20) | YES | | NULL | | 34 +-------+-------------+------+-----+---------+-------+ 35 2 rows in set (0.00 sec) 36 37 MariaDB [zxg]> alter table t1 modify column name varchar(20); 38 Query OK, 0 rows affected (0.00 sec) 39 Records: 0 Duplicates: 0 Warnings: 0 40 41 MariaDB [zxg]> update t1 set name='zxg.net' where id=1; 42 Query OK, 1 row affected (0.00 sec) 43 Rows matched: 1 Changed: 1 Warnings: 0 44 45 MariaDB [zxg]>
三、mysql 数据库字符集设置
mysql数据库存储数据时，默认编码为latinl，存储中文字符时，在调用时会显示为乱码，为了解决该乱码问题，需修改该mysql默认字符集为UTE-8
装mariadb的是时候看已经默认为ute-8
MariaDB [(none)]> show variables like '%char%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec)
如不是，可以设置
SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;
四、mysql 数据库密码管理
设置密码访问，密码破解、密码权限、修改密码；
1、创建用户及授权
grant all on zxg.* to test@localhost identified by 'pas';
grant select,insert,update,delete on *.*to test@"%" identified by 'pas';
grant all on zxg.* to test@`192.168.216.53` identified by 'pas'
2、破解密码方法
停止服务---》跳过权限方式启动---》单开一个窗口登陆---》登陆修改密码即可
1）systemctl stop mariadb
2）mysqld_safe --skip-grant-tables &
[root@web2 ~]# mysqld_safe --skip-grant-tables & [1] 47542 [root@web2 ~]# 190520 15:45:22 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'. 190520 15:45:22 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
[root@web2 ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 1 Server version: 5.5.60-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [mysql]> update user set password=password('11111111') where user='root' ; Query OK, 3 rows affected (0.01 sec) Rows matched: 4 Changed: 3 Warnings: 0 MariaDB [mysql]> flush privileges; Query OK, 0 rows affected (0.00 sec) MariaDB [mysql]> exit Bye [root@web2 ~]#
然后退出”跳过权限方式“ ctrl+c ，正常启动mysql就可以了
五、mysql 配置文件详解
1、参数说明
[mysqld] #服务端配置 port = 3306 #监听端口 socket = /tmp/mysql.sock #通信设置 user = mariadb #使用mariadb用户启动 basedir = /usr/local/mariadb #安装路径 datadir = /data/mysql #数据目录 log_error = /data/mysql/mariadb.err #错误日志 pid-file = /data/mysql/mariadb.pid #pid进程文件 skip-external-locking #避免mysql的外部锁定，减少出错几率提高稳定性 key_buffer_size = 64M #缓存存储引擎参数，这个参数可以设置为64M max_allowed_packet = 1M #允许最大接收数据包的大小，防止服务器发送过大的数据包，可以设置为16MB或者更大，但设置太大也可能有危险 table_open_cache = 256 #mysql每打开一个表，都会读入一些数据到table_open_cache缓存中，当MYSQL在这个缓存中找不到相应的信息时，才会去磁盘读取，默认值64，假设系统有200个并发连接，则需将此参数设置为200*N（N为每个连接所需的文件描述符数目）；当设置为很大时，如果系统处理不了那么多文件描述符，那么就会出现客户端失效，连接不上 sort_buffer_size = 1M #在表进行order by和group by 排序操作时，由于排序的字段没有索引，会出现Using filesort，为了提高性能，可用此参数增加每个线程分配的缓存区大小，默认时256k，这个参数不要设置过大，一般128~256k，另外一般出现using filesort的时候，要通过增加索引来解决 net_buffer_length = 8K #包消息缓冲区初始化net_buffer_length字节，但需要时可以增长到max_allowed_packet字节 read_buffer_size = 1M #该参数用于表的顺序扫描，表示每个线程分配的缓冲区大小，比如在进行全表扫描时，mysql会按照数据的存储顺序一次读取数据块，每次读取的数据块首先会暂存在read_buffer_size中，当buffer空间被写满或者全部数据读取结束后，在将buffer中的数据返回给上层调用者，以提高效率默认128k，也不要设置过大 read_rnd_buffer_size = 512K #该参数用于表的随机读取，表示每个线程分配的缓冲区大小，比如，按照一个非索引字段做order by排序操作时，就会利用这个缓冲区来暂存读取的数据，默认时256k，也不要设置过大 myisam_sort_buffer_size = 16M #当myisam表执行repair table或创建索引时，用以缓冲排序索引，设置太小可能会遇到"myisam_sort_buffer_size is to small" thread_cache_size = 32 #线程池，线程缓冲。用来缓冲空闲的线程，以至于不被销毁，如果线程缓冲在的空闲线程，需要重新建立新连接，则会优先调用线程池中的缓冲，很快就能相应连接请求，每建立一个连接，都需要一个线程与之匹配。 query_cache_size = 32M #缓存select语句和结果集大小的参数。查询缓存会存储一个select查询的文本与被传送到客户端的相应结果。如果之后接收到一个相同的查询，服务器会从查询缓存中检索结果，而不是再次分析和执行这个同样的查询。如果你的环境中写操作很少，读操作频繁，那么打开query_cache_type=1，会对性能有明显提升。如果写操作频繁，则应该关闭它（query_cache_type=0）。 tmp_table_size = 64M #临时HEAP数据表的最大长度(默认设置是32M); 超过这个长度的临时数据表将被转换为MyISAM数据表并存入一个临时文件。 # explicit_defaults_for_timestamp = true #是否显示默认时间戳 #skip-networking # max_connections = 500 #该参数用来设置最大连接数，告诉你当前你的服务器允许多少并发连接。默认为100，一般设置为512-1000即可。请记住，太多的连接会导致内存的使用量过高并且会锁住你的 MySQL 服务器。一般小网站需要 100-200 的连接数，而较大可能需要 500-800 甚至更多。这里的值很大程度上取决于你 MySQL/MariaDB 的使用情况。 max_connect_errors = 100 #如果有时网络抽风，或者应用配置错误，或者其他原因导致客户端短时间内不断的尝试连接，客户端可能会被列入黑名单，然后将无法连接，直到再次刷新主机缓存。这个选项默认值太小了，可以考虑设的足够大（如果你的服务器配置够强大的话）。 open_files_limit = 65535 #mysql打开最大文件数 # log-bin=mysql-bin #这些路径相对于datadir binlog_format=mixed #日志格式 server-id = 1 #给服务器分配一个独一无二的ID编号; n的取值范围是1~2的32次方启用二进制日志功能。在复制数据同步的时候会用到，Helloweba后面会有文章介绍。 expire_logs_days = 10 #启用二进制日志后，保留日志的天数。服务器会自动清理指定天数前的日志文件，如果不设置则会导致服务器空间耗尽。一般设置为7～14天。 # default_storage_engine = InnoDB #新数据表的默认存储引擎(默认设置是MyISAM)。这项设置还可以通过–default-table-type选项来设置。 innodb_file_per_table = 1 #提供了更灵活的方式，它把每个数据库的信息保存在一个 .ibd 数据文件中。每个 .idb 文件代表它自己的表空间。通过这样的方式可以更快地完成类似 “TRUNCATE” 的数据库操作，当删除或截断一个数据库表时，你也可以回收未使用的空间。这样配置的另一个好处是你可以将某些数据库表放在一个单独的存储设备。这可以大大提升你磁盘的 I/O 负载。 innodb_data_home_dir = /data/mysql #InnoDB主目录，所有与InnoDB数据表有关的目录或文件路径都相对于这个路径。在默认的情况下，这个主目录就是MySQL的数据目录。 innodb_data_file_path = ibdata1:10M:autoextend #用来容纳InnoDB为数据表的表空间: 可能涉及一个以上的文件; 每一个表空间文件的最大长度都必须以字节(B)、兆字节(MB)或千兆字节(GB)为单位给出; 表空间文件的名字必须以分号隔开; 最后一个表空间文件还可以带一个autoextend属性和一个最大长度(max:n)。 innodb_log_group_home_dir = /data/mysql #用来存放InnoDB日志文件的目录路径(如ib_logfile0、ib_logfile1等)。在默认的情况下，InnoDB驱动程序将使用 MySQL数据目录作为自己保存日志文件的位置。 innodb_buffer_pool_size = 256M #这个参数是InnoDB存储引擎的核心参数，默认为128KB，这个参数要设置为物理内存的60%～70%。 innodb_log_file_size = 64M #事务日志文件写操作缓存区的最大长度(默认设置是1MB)。 innodb_log_buffer_size = 8M #事务日志所使用的缓存区。InnoDB在写事务日志的时候，为了提高性能，先将信息写入Innodb Log Buffer中，当满足innodb_flush_log_trx_commit参数所设置的相应条件（或者日志缓冲区写满）时，再将日志写到文件（或者同步到磁盘）中。可以通过innodb_log_buffer_size参数设置其可以使用的最大内存空间。默认是8MB，一般为16～64MB即可。 innodb_flush_log_at_trx_commit = 1 #这个选项决定着什么时候把日志信息写入日志文件以及什么时候把这些文件物理地写(术语称为”同步”)到硬盘上。设置值0的意思是每隔一秒写一次日 志并进行 同步，这可以减少硬盘写操作次数，但可能造成数据丢失; 设置值1(设置设置)的意思是在每执行完一条COMMIT命令就写一次日志并进行同步，这可以防止数据丢失，但硬盘写操作可能会很频繁; 设置值2是一般折衷的办法，即每执行完一条COMMIT命令写一次日志，每隔一秒进行一次同步。 innodb_lock_wait_timeout = 50 #如果某个事务在等待n秒(s)后还没有获得所需要的资源，就使用ROLLBACK命令放弃这个事务。这项设置对于发现和处理未能被InnoDB数据表驱动 程序识别出来的死锁条件有着重要的意义。这个选项的默认设置是50s。 # [mysqldump] # quick # max_allowed_packet = 16M # # [mysql] # no-auto-rehash # # [myisamchk] # key_buffer_size = 64M # sort_buffer_size = 1M # read_buffer = 2M # write_buffer = 2M # # [mysqlhotcopy] # interactive-timeout #
2、mysql数据库索引案例（百万量级）
[client]
port =3306 socket =/tmp/mysql.sock [mysqld] port = 3306 socket = /tmp/mysql.sock user = mysql
server_id = 10
datadir = /data/mysql/
old_passwords = 1
lower_case_table_names = 1
character-set-server = utf8
default-storage-engine = MYISAM
log-bin = bin.log
log-error = error.log
pid-file = mysql.pid
long_query_time = 2
slow_query_time = 2
slow_query_log
slow_query_log_file = slow.log
binlog_cache_size = 4MB
binlog_format = mixed
max_binlog_cache_size = 16MB
max_binlog_size = 1GB
expire_logs_days = 30
ft_min_word_len = 4
back_log = 512
max_allowed_packet = 64MB
max_connections = 4096
max_connect_errors = 100
join_buffer_size = 2MB
read_buffer_size = 2MB
read_rnd_buffer_size = 2MB
sort_buffer_size = 2MB
query_cache_size = 2MB
table_open_cache = 10000
thread_cache_size = 256
max_heap_table_size = 64MB
tmp_table_size = 64MB
thread_stack = 192KB
thread_concurrency = 24
local-infile = 0
skip-show-database
skip-name-resolve
skip-external-locking
connect_timeout = 600
interactive_timeout = 600
wait_timeout = 600
#MyISAM
key_buffer_size = 512MB
bulk_insert_buffer_size = 64MB
mysiam_sort_buffer_size = 64MB
mysiam_max_sort_file_size = 1GB
mysiam_repair_threads = 1
concurrent_insert = 2
myisam_recover
#INNODB
innodb_buffer_pool_size = 64G
innodb_additional_mem_pool_size = 32MB
innodb_data_file_path = ibdata1:1G;ibdata2:1G:autoextend
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_file_per_table = 1
innodb_flush_log_at_thx_commit = 2
innodb_lock_wait_timeout = 120
innodb_log_buffer_size = 8MB
innodb_log_file_size = 256MB
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_thread_concurrency = 16
innodb_open_files = 10000
#innodb_force_recovery = 4
#replication slave
read-only
#skip-salve-start
relay-log = relay.log
log-slave-updates
本文参考：
《曝光：Linux企业运维实战》
