Mariadb/MySQL安装方式实战篇
作者:尹正杰
版权声明:原创作品,谢绝转载!否则将追究法律责任。
一.如何选择MySQL的安装方式
1>.选择安装的MySQL版本
1.首先判断是否要和公司其他已经安装好的MySQL保持版本一致; 2.如果没有上述要求,则一般会安装最新版本(截止2019年1月初,目前最新MySQL版本是8.0.13); 3.如果不是实验新功能特性,则不要选择development release,而要安装General Available(GA)release(代表稳定版本,可在生产环境中使用);
2>.下载正确的tar包
下载地址:https://dev.mysql.com/downloads/mysql/。
3>.如何选择MySQL的安装方式
1.程序包管理器管理yum/rpm方式安装mysql yum/rpm方式安装mysql安装极其简单,只要执行 yum -y install mysql-server即可。 yum/rpm安装方式安装MySQL应用场景: 适合对数据库要求不太高的场合,例如并发不大,公司内部,企业内部的一些应用场景。大的门户网站公司他们把源码根据企业的需求制作出rpm,搭建yum仓库,yum -y install xxx 2.源码安装mysql 此种方法适合所有的MySQL5.0.xx-5.1.xx产品系列,是最常规的编译方式。 source code,一般情况下会选择二进制安装方式,如果有特殊需求,比如修改一部源码或修改MySQL深层次的配置,则会选择源码方式。 3.采用cmake方式编译安装MySQL 由于MySQL5.5-5.6.xx产品系列特殊性,所以编译方式也和早期的产品安装方式不同,采用cmake或gmake方式编译安装。 4.采用二进制格式的程序包(展开至特定路径,并经过简单配置后即可使用)方式免编译安装MySQL 二进制安装包的方式(RPM包,ZIP,Tar等),这种方法和yum /rpm 包安装方式类似,适合各类MySQL产品系列,不需要复杂的编译设置及编译时间的等待,直接解压下载的软件包,初始化即可完成MySQL的安装启动 如何选择MySQL的安装方式 yum/rpm方式安装MySQL适合于对数据库要求不太高的场合,例如开发不大,公司内部,企业内部的一些应用场景。 二进制免安装比较简单方便,适合5.0-5.1和5.5-5.6系列,是很多DBA的选择,普通Linux运维人员多采用编译的方式,5.0-5.1系列就是常规编译方式,5.5-5.6系列就是cmake编译方式。 推荐:MySQL5.5及以上的版本;安装方式机器数量少的话,使用cmake编译方式;数量多的话采用二进制免安装。 博主推荐阅读: 使用二进制安装包的方式单机部署MySQL8.0.13(https://www.cnblogs.com/yinzhengjie/p/10201038.html) 使用yum源的方式单机部署MySQL8.0.13(https://www.cnblogs.com/yinzhengjie/p/10212038.html)
二.基于yum方式安装MySQL
在CentOS 6.x版本中的确存在MySQL的安装包,但CentOS 7.x之后版本,MySQL的yum源被踢出,取而代之的是MariaDB源。 因此,我们想要在CentOS 7.x版本上安装MySQL,就需要查看MySQl官网提供的yum资源(https://dev.mysql.com/downloads/repo/yum/) 博主推荐阅读: 使用yum源的方式单机部署MySQL8.0.13(https://www.cnblogs.com/yinzhengjie/p/10212038.html)
1>.安装MySQL的yun源
[root@node101.yinzhengjie.org.cn ~]# wget https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm #下载官方的MySQLyum源,你也可以在阿里源中下载对应的源。 --2019-10-24 18:24:58-- https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm Resolving dev.mysql.com (dev.mysql.com)... 137.254.60.11 Connecting to dev.mysql.com (dev.mysql.com)|137.254.60.11|:443... connected. HTTP request sent, awaiting response... 200 OK Length: 25820 (25K) [application/x-redhat-package-manager] Saving to: ‘mysql80-community-release-el7-1.noarch.rpm’ 100%[=============================================================================================>] 25,820 165KB/s in 0.2s 2019-10-24 18:24:50 (165 KB/s) - ‘mysql80-community-release-el7-1.noarch.rpm’ saved [25820/25820] [root@node101.yinzhengjie.org.cn ~]# [root@node101.yinzhengjie.org.cn ~]# ll total 28 -rw-r--r-- 1 root root 25820 Apr 18 2018 mysql80-community-release-el7-1.noarch.rpm [root@node101.yinzhengjie.org.cn ~]#
[root@node101.yinzhengjie.org.cn ~]# ll /etc/yum.repos.d/ | grep mysql [root@node101.yinzhengjie.org.cn ~]# [root@node101.yinzhengjie.org.cn ~]# rpm -ivh mysql80-community-release-el7-1.noarch.rpm #安装刚刚从官方下载的yum源,会在"/etc/yum.repos.d/"下生成相应的mysql社区版的repo配置文件。 warning: mysql80-community-release-el7-1.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY Preparing... ################################# [100%] Updating / installing... 1:mysql80-community-release-el7-1 ################################# [100%] [root@node101.yinzhengjie.org.cn ~]# [root@node101.yinzhengjie.org.cn ~]# ll /etc/yum.repos.d/ | grep mysql -rw-r--r-- 1 root root 1864 Feb 22 2018 mysql-community.repo -rw-r--r-- 1 root root 1885 Feb 22 2018 mysql-community-source.repo [root@node101.yinzhengjie.org.cn ~]# [root@node101.yinzhengjie.org.cn ~]#
2>.安装MySQL社区版服务
[root@node101.yinzhengjie.org.cn ~]# grep keepcache /etc/yum.conf keepcache=0 [root@node101.yinzhengjie.org.cn ~]# [root@node101.yinzhengjie.org.cn ~]# sed -i s'#keepcache=0#keepcache=1#' /etc/yum.conf #我们开启rpm缓存,使得安装服务时下载的安装包不会被自动删除 [root@node101.yinzhengjie.org.cn ~]# [root@node101.yinzhengjie.org.cn ~]# grep keepcache /etc/yum.conf keepcache=1 [root@node101.yinzhengjie.org.cn ~]# [root@node101.yinzhengjie.org.cn ~]# ll /etc/yum.repos.d/ #默认存放yum服务的配置文件路径 total 44 -rw-r--r--. 1 root root 1664 Nov 23 2018 CentOS-Base.repo -rw-r--r--. 1 root root 1309 Nov 23 2018 CentOS-CR.repo -rw-r--r--. 1 root root 649 Nov 23 2018 CentOS-Debuginfo.repo -rw-r--r--. 1 root root 314 Nov 23 2018 CentOS-fasttrack.repo -rw-r--r--. 1 root root 630 Nov 23 2018 CentOS-Media.repo -rw-r--r--. 1 root root 1331 Nov 23 2018 CentOS-Sources.repo -rw-r--r--. 1 root root 5701 Nov 23 2018 CentOS-Vault.repo -rw-r--r-- 1 root root 2640 Oct 17 21:12 docker-ce.repo -rw-r--r-- 1 root root 1864 Feb 22 2018 mysql-community.repo -rw-r--r-- 1 root root 1885 Feb 22 2018 mysql-community-source.repo [root@node101.yinzhengjie.org.cn ~]# [root@node101.yinzhengjie.org.cn ~]# ll /var/cache/yum/$basearch/$releasever/x86_64/7 #默认存放下载所有rpm安装包的路径。 total 8 drwxr-xr-x. 4 root root 278 Oct 20 07:49 base drwxr-xr-x 4 root root 251 Oct 20 07:49 docker-ce-stable drwxr-xr-x. 4 root root 183 Oct 20 07:49 extras -rw-r--r-- 1 root root 580 Oct 20 07:49 timedhosts -rw-r--r--. 1 root root 517 Oct 18 20:24 timedhosts.txt drwxr-xr-x. 4 root root 183 Oct 20 07:49 updates [root@node101.yinzhengjie.org.cn ~]# [root@node101.yinzhengjie.org.cn ~]#
[root@node101.yinzhengjie.org.cn ~]# yum -y install mysql-community-server #安装MySQL社区版 Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: mirrors.nju.edu.cn * extras: mirrors.nju.edu.cn * updates: mirrors.cn99.com base | 3.6 kB 00:00:00 docker-ce-stable | 3.5 kB 00:00:00 extras | 2.9 kB 00:00:00 mysql-connectors-community | 2.5 kB 00:00:00 mysql-tools-community | 2.5 kB 00:00:00 mysql80-community | 2.5 kB 00:00:00 updates | 2.9 kB 00:00:00 (1/5): extras/7/x86_64/primary_db | 153 kB 00:00:00 (2/5): mysql-tools-community/x86_64/primary_db | 66 kB 00:00:01 (3/5): mysql80-community/x86_64/primary_db | 87 kB 00:00:01 (4/5): mysql-connectors-community/x86_64/primary_db | 48 kB 00:00:06 (5/5): updates/7/x86_64/primary_db | 2.8 MB 00:00:20 Resolving Dependencies --> Running transaction check ---> Package mysql-community-server.x86_64 0:8.0.18-1.el7 will be installed --> Processing Dependency: mysql-community-common(x86-64) = 8.0.18-1.el7 for package: mysql-community-server-8.0.18-1.el7.x86_64 --> Processing Dependency: mysql-community-client(x86-64) >= 8.0.11 for package: mysql-community-server-8.0.18-1.el7.x86_64 --> Running transaction check ---> Package mysql-community-client.x86_64 0:8.0.18-1.el7 will be installed --> Processing Dependency: mysql-community-libs(x86-64) >= 8.0.11 for package: mysql-community-client-8.0.18-1.el7.x86_64 ---> Package mysql-community-common.x86_64 0:8.0.18-1.el7 will be installed --> Running transaction check ---> Package mariadb-libs.x86_64 1:5.5.60-1.el7_5 will be obsoleted --> Processing Dependency: libmysqlclient.so.18()(64bit) for package: 2:postfix-2.10.1-7.el7.x86_64 --> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: 2:postfix-2.10.1-7.el7.x86_64 ---> Package mysql-community-libs.x86_64 0:8.0.18-1.el7 will be obsoleting --> Running transaction check ---> Package mysql-community-libs-compat.x86_64 0:8.0.18-1.el7 will be obsoleting --> Finished Dependency Resolution Dependencies Resolved ======================================================================================================================================= Package Arch Version Repository Size ======================================================================================================================================= Installing: mysql-community-libs x86_64 8.0.18-1.el7 mysql80-community 3.7 M replacing mariadb-libs.x86_64 1:5.5.60-1.el7_5 mysql-community-libs-compat x86_64 8.0.18-1.el7 mysql80-community 1.3 M replacing mariadb-libs.x86_64 1:5.5.60-1.el7_5 mysql-community-server x86_64 8.0.18-1.el7 mysql80-community 429 M Installing for dependencies: mysql-community-client x86_64 8.0.18-1.el7 mysql80-community 38 M mysql-community-common x86_64 8.0.18-1.el7 mysql80-community 597 k Transaction Summary ======================================================================================================================================= Install 3 Packages (+2 Dependent packages) Total download size: 473 M Downloading packages: warning: /var/cache/yum/x86_64/7/mysql80-community/packages/mysql-community-common-8.0.18-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signatur e, key ID 5072e1f5: NOKEYPublic key for mysql-community-common-8.0.18-1.el7.x86_64.rpm is not installed (1/5): mysql-community-common-8.0.18-1.el7.x86_64.rpm | 597 kB 00:00:07 (2/5): mysql-community-libs-8.0.18-1.el7.x86_64.rpm | 3.7 MB 00:00:02 (3/5): mysql-community-libs-compat-8.0.18-1.el7.x86_64.rpm | 1.3 MB 00:00:00 (4/5): mysql-community-server-8.0.18-1.el7.x86_64.rpm | 429 MB 00:05:17 (5/5): mysql-community-client-8.0.18-1.el7.x86_64.rp 94% [========================================- ] 14 kB/s | 447 MB 00:33:12 ETA (5/5): mysql-community-client-8.0.18-1.el7.x86_64.rp 94% [========================================- ] 14 kB/s | 447 MB 00:31:38 ETA (5/5): mysql-community-client-8.0.18-1.el7.x86_64.rpm | 38 MB 00:59:40 --------------------------------------------------------------------------------------------------------------------------------------- Total 135 kB/s | 473 MB 00:59:40 Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql Importing GPG key 0x5072E1F5: Userid : "MySQL Release Engineering <mysql-build@oss.oracle.com>" Fingerprint: a4a9 4068 76fc bd3c 4567 70c8 8c71 8d3b 5072 e1f5 Package : mysql80-community-release-el7-1.noarch (installed) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql Running transaction check Running transaction test Transaction test succeeded Running transaction Warning: RPMDB altered outside of yum. Installing : mysql-community-common-8.0.18-1.el7.x86_64 1/6 Installing : mysql-community-libs-8.0.18-1.el7.x86_64 2/6 Installing : mysql-community-client-8.0.18-1.el7.x86_64 3/6 Installing : mysql-community-server-8.0.18-1.el7.x86_64 4/6 Installing : mysql-community-libs-compat-8.0.18-1.el7.x86_64 5/6 Erasing : 1:mariadb-libs-5.5.60-1.el7_5.x86_64 6/6 Verifying : mysql-community-client-8.0.18-1.el7.x86_64 1/6 Verifying : mysql-community-common-8.0.18-1.el7.x86_64 2/6 Verifying : mysql-community-server-8.0.18-1.el7.x86_64 3/6 Verifying : mysql-community-libs-8.0.18-1.el7.x86_64 4/6 Verifying : mysql-community-libs-compat-8.0.18-1.el7.x86_64 5/6 Verifying : 1:mariadb-libs-5.5.60-1.el7_5.x86_64 6/6 Installed: mysql-community-libs.x86_64 0:8.0.18-1.el7 mysql-community-libs-compat.x86_64 0:8.0.18-1.el7 mysql-community-server.x86_64 0:8.0.18-1.el7 Dependency Installed: mysql-community-client.x86_64 0:8.0.18-1.el7 mysql-community-common.x86_64 0:8.0.18-1.el7 Replaced: mariadb-libs.x86_64 1:5.5.60-1.el7_5 Complete! [root@node101.yinzhengjie.org.cn ~]# [root@node101.yinzhengjie.org.cn ~]#
[root@node101.yinzhengjie.org.cn ~]# rpm -ql mysql-community-server #查看安装包中带有的文件列表 /etc/logrotate.d/mysql /etc/my.cnf /etc/my.cnf.d /usr/bin/ibd2sdi /usr/bin/innochecksum /usr/bin/lz4_decompress /usr/bin/my_print_defaults /usr/bin/myisam_ftdump /usr/bin/myisamchk /usr/bin/myisamlog /usr/bin/myisampack /usr/bin/mysql_secure_installation /usr/bin/mysql_ssl_rsa_setup /usr/bin/mysql_tzinfo_to_sql /usr/bin/mysql_upgrade /usr/bin/mysqld_pre_systemd /usr/bin/mysqldumpslow /usr/bin/perror /usr/bin/zlib_decompress /usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/mysqld@.service /usr/lib/tmpfiles.d/mysql.conf /usr/lib64/mysql/mecab /usr/lib64/mysql/mecab/dic /usr/lib64/mysql/mecab/dic/ipadic_euc-jp /usr/lib64/mysql/mecab/dic/ipadic_euc-jp/char.bin /usr/lib64/mysql/mecab/dic/ipadic_euc-jp/dicrc /usr/lib64/mysql/mecab/dic/ipadic_euc-jp/left-id.def /usr/lib64/mysql/mecab/dic/ipadic_euc-jp/matrix.bin /usr/lib64/mysql/mecab/dic/ipadic_euc-jp/pos-id.def /usr/lib64/mysql/mecab/dic/ipadic_euc-jp/rewrite.def /usr/lib64/mysql/mecab/dic/ipadic_euc-jp/right-id.def /usr/lib64/mysql/mecab/dic/ipadic_euc-jp/sys.dic /usr/lib64/mysql/mecab/dic/ipadic_euc-jp/unk.dic /usr/lib64/mysql/mecab/dic/ipadic_sjis /usr/lib64/mysql/mecab/dic/ipadic_sjis/char.bin /usr/lib64/mysql/mecab/dic/ipadic_sjis/dicrc /usr/lib64/mysql/mecab/dic/ipadic_sjis/left-id.def /usr/lib64/mysql/mecab/dic/ipadic_sjis/matrix.bin /usr/lib64/mysql/mecab/dic/ipadic_sjis/pos-id.def /usr/lib64/mysql/mecab/dic/ipadic_sjis/rewrite.def /usr/lib64/mysql/mecab/dic/ipadic_sjis/right-id.def /usr/lib64/mysql/mecab/dic/ipadic_sjis/sys.dic /usr/lib64/mysql/mecab/dic/ipadic_sjis/unk.dic /usr/lib64/mysql/mecab/dic/ipadic_utf-8 /usr/lib64/mysql/mecab/dic/ipadic_utf-8/char.bin /usr/lib64/mysql/mecab/dic/ipadic_utf-8/dicrc /usr/lib64/mysql/mecab/dic/ipadic_utf-8/left-id.def /usr/lib64/mysql/mecab/dic/ipadic_utf-8/matrix.bin /usr/lib64/mysql/mecab/dic/ipadic_utf-8/pos-id.def /usr/lib64/mysql/mecab/dic/ipadic_utf-8/rewrite.def /usr/lib64/mysql/mecab/dic/ipadic_utf-8/right-id.def /usr/lib64/mysql/mecab/dic/ipadic_utf-8/sys.dic /usr/lib64/mysql/mecab/dic/ipadic_utf-8/unk.dic /usr/lib64/mysql/mecab/etc /usr/lib64/mysql/mecab/etc/mecabrc /usr/lib64/mysql/plugin /usr/lib64/mysql/plugin/adt_null.so /usr/lib64/mysql/plugin/auth_socket.so /usr/lib64/mysql/plugin/authentication_ldap_sasl_client.so /usr/lib64/mysql/plugin/component_audit_api_message_emit.so /usr/lib64/mysql/plugin/component_log_filter_dragnet.so /usr/lib64/mysql/plugin/component_log_sink_json.so /usr/lib64/mysql/plugin/component_log_sink_syseventlog.so /usr/lib64/mysql/plugin/component_mysqlbackup.so /usr/lib64/mysql/plugin/component_validate_password.so /usr/lib64/mysql/plugin/connection_control.so /usr/lib64/mysql/plugin/ddl_rewriter.so /usr/lib64/mysql/plugin/debug /usr/lib64/mysql/plugin/debug/adt_null.so /usr/lib64/mysql/plugin/debug/auth_socket.so /usr/lib64/mysql/plugin/debug/authentication_ldap_sasl_client.so /usr/lib64/mysql/plugin/debug/component_audit_api_message_emit.so /usr/lib64/mysql/plugin/debug/component_log_filter_dragnet.so /usr/lib64/mysql/plugin/debug/component_log_sink_json.so /usr/lib64/mysql/plugin/debug/component_log_sink_syseventlog.so /usr/lib64/mysql/plugin/debug/component_mysqlbackup.so /usr/lib64/mysql/plugin/debug/component_validate_password.so /usr/lib64/mysql/plugin/debug/connection_control.so /usr/lib64/mysql/plugin/debug/ddl_rewriter.so /usr/lib64/mysql/plugin/debug/group_replication.so /usr/lib64/mysql/plugin/debug/ha_example.so /usr/lib64/mysql/plugin/debug/ha_mock.so /usr/lib64/mysql/plugin/debug/innodb_engine.so /usr/lib64/mysql/plugin/debug/keyring_file.so /usr/lib64/mysql/plugin/debug/keyring_udf.so /usr/lib64/mysql/plugin/debug/libmemcached.so /usr/lib64/mysql/plugin/debug/libpluginmecab.so /usr/lib64/mysql/plugin/debug/locking_service.so /usr/lib64/mysql/plugin/debug/mypluglib.so /usr/lib64/mysql/plugin/debug/mysql_clone.so /usr/lib64/mysql/plugin/debug/mysql_no_login.so /usr/lib64/mysql/plugin/debug/rewrite_example.so /usr/lib64/mysql/plugin/debug/rewriter.so /usr/lib64/mysql/plugin/debug/semisync_master.so /usr/lib64/mysql/plugin/debug/semisync_slave.so /usr/lib64/mysql/plugin/debug/validate_password.so /usr/lib64/mysql/plugin/debug/version_token.so /usr/lib64/mysql/plugin/group_replication.so /usr/lib64/mysql/plugin/ha_example.so /usr/lib64/mysql/plugin/ha_mock.so /usr/lib64/mysql/plugin/innodb_engine.so /usr/lib64/mysql/plugin/keyring_file.so /usr/lib64/mysql/plugin/keyring_udf.so /usr/lib64/mysql/plugin/libmemcached.so /usr/lib64/mysql/plugin/libpluginmecab.so /usr/lib64/mysql/plugin/locking_service.so /usr/lib64/mysql/plugin/mypluglib.so /usr/lib64/mysql/plugin/mysql_clone.so /usr/lib64/mysql/plugin/mysql_no_login.so /usr/lib64/mysql/plugin/rewrite_example.so /usr/lib64/mysql/plugin/rewriter.so /usr/lib64/mysql/plugin/semisync_master.so /usr/lib64/mysql/plugin/semisync_slave.so /usr/lib64/mysql/plugin/validate_password.so /usr/lib64/mysql/plugin/version_token.so /usr/lib64/mysql/private /usr/lib64/mysql/private/libprotobuf-lite.so.3.6.1 /usr/lib64/mysql/private/libprotobuf.so.3.6.1 /usr/sbin/mysqld /usr/sbin/mysqld-debug /usr/share/doc/mysql-community-server-8.0.18 /usr/share/doc/mysql-community-server-8.0.18/INFO_BIN /usr/share/doc/mysql-community-server-8.0.18/INFO_SRC /usr/share/doc/mysql-community-server-8.0.18/LICENSE /usr/share/doc/mysql-community-server-8.0.18/README /usr/share/man/man1/ibd2sdi.1.gz /usr/share/man/man1/innochecksum.1.gz /usr/share/man/man1/lz4_decompress.1.gz /usr/share/man/man1/my_print_defaults.1.gz /usr/share/man/man1/myisam_ftdump.1.gz /usr/share/man/man1/myisamchk.1.gz /usr/share/man/man1/myisamlog.1.gz /usr/share/man/man1/myisampack.1.gz /usr/share/man/man1/mysql.server.1.gz /usr/share/man/man1/mysql_secure_installation.1.gz /usr/share/man/man1/mysql_ssl_rsa_setup.1.gz /usr/share/man/man1/mysql_tzinfo_to_sql.1.gz /usr/share/man/man1/mysql_upgrade.1.gz /usr/share/man/man1/mysqldumpslow.1.gz /usr/share/man/man1/mysqlman.1.gz /usr/share/man/man1/perror.1.gz /usr/share/man/man1/zlib_decompress.1.gz /usr/share/man/man8/mysqld.8.gz /usr/share/mysql-8.0/dictionary.txt /usr/share/mysql-8.0/innodb_memcached_config.sql /usr/share/mysql-8.0/install_rewriter.sql /usr/share/mysql-8.0/mysql-log-rotate /usr/share/mysql-8.0/uninstall_rewriter.sql /var/lib/mysql /var/lib/mysql-files /var/lib/mysql-keyring /var/run/mysqld [root@node101.yinzhengjie.org.cn ~]#
[root@node101.yinzhengjie.org.cn ~]# ll /etc/my.cnf #MySQL的客户端和服务端均使用该配置文件 -rw-r--r-- 1 root root 1243 Sep 20 17:33 /etc/my.cnf [root@node101.yinzhengjie.org.cn ~]# [root@node101.yinzhengjie.org.cn ~]# rpm -qf /etc/my.cnf #查看MySQL的配置文件来自于哪个安装包。 mysql-community-server-8.0.18-1.el7.x86_64 [root@node101.yinzhengjie.org.cn ~]# [root@node101.yinzhengjie.org.cn ~]#
[root@node101.yinzhengjie.org.cn ~]# ll /var/cache/yum/$basearch/$releasever/x86_64/7 #安装MySQL服务端成功后,观察该目录下缓存相应的rpm包,可以下载下来保存到本地用于制作公司私有的yum仓库便于网络下载更便捷。 total 8 drwxr-xr-x. 4 root root 278 Oct 25 05:50 base drwxr-xr-x 4 root root 251 Oct 25 05:50 docker-ce-stable drwxr-xr-x. 4 root root 183 Oct 25 05:50 extras drwxr-xr-x 4 root root 137 Oct 25 05:50 mysql80-community drwxr-xr-x 4 root root 137 Oct 25 05:50 mysql-connectors-community drwxr-xr-x 4 root root 137 Oct 25 05:50 mysql-tools-community -rw-r--r-- 1 root root 622 Oct 25 05:52 timedhosts -rw-r--r--. 1 root root 517 Oct 18 20:24 timedhosts.txt drwxr-xr-x. 4 root root 183 Oct 25 05:50 updates [root@node101.yinzhengjie.org.cn ~]# [root@node101.yinzhengjie.org.cn ~]# [root@node101.yinzhengjie.org.cn ~]# ll /var/cache/yum/$basearch/$releasever/x86_64/7/mysql* -R /var/cache/yum///x86_64/7/mysql80-community: total 96 -rw-r--r-- 1 root root 0 Oct 25 05:50 cachecookie -rw-r--r-- 1 root root 89466 Oct 10 19:46 f0cd25635b07e788d2c16d21791afd99f3e9e3d8-primary.sqlite.bz2 drwxr-xr-x 2 root root 31 Oct 24 18:37 gen drwxr-xr-x 2 root root 279 Oct 24 18:37 packages -rw-r--r-- 1 root root 2524 Oct 10 19:46 repomd.xml /var/cache/yum///x86_64/7/mysql80-community/gen: total 588 -rw-r--r-- 1 root root 600064 Oct 10 19:46 primary_db.sqlite /var/cache/yum///x86_64/7/mysql80-community/packages: total 484620 -rw-r--r-- 1 root root 40104640 Oct 10 19:45 mysql-community-client-8.0.18-1.el7.x86_64.rpm -rw-r--r-- 1 root root 611436 Oct 10 19:45 mysql-community-common-8.0.18-1.el7.x86_64.rpm -rw-r--r-- 1 root root 3877664 Oct 10 19:45 mysql-community-libs-8.0.18-1.el7.x86_64.rpm -rw-r--r-- 1 root root 1363968 Oct 10 19:45 mysql-community-libs-compat-8.0.18-1.el7.x86_64.rpm -rw-r--r-- 1 root root 450282440 Oct 10 19:45 mysql-community-server-8.0.18-1.el7.x86_64.rpm /var/cache/yum///x86_64/7/mysql-connectors-community: total 52 -rw-r--r-- 1 root root 0 Oct 25 05:50 cachecookie -rw-r--r-- 1 root root 48722 Oct 12 15:46 eb4609f765d6a231876f145d941bc65ab2964300-primary.sqlite.bz2 drwxr-xr-x 2 root root 31 Oct 24 18:37 gen drwxr-xr-x 2 root root 6 Oct 24 18:36 packages -rw-r--r-- 1 root root 2516 Oct 12 15:46 repomd.xml /var/cache/yum///x86_64/7/mysql-connectors-community/gen: total 360 -rw-r--r-- 1 root root 367616 Oct 12 15:46 primary_db.sqlite /var/cache/yum///x86_64/7/mysql-connectors-community/packages: total 0 /var/cache/yum///x86_64/7/mysql-tools-community: total 72 -rw-r--r-- 1 root root 0 Oct 25 05:50 cachecookie -rw-r--r-- 1 root root 67711 Oct 12 03:05 fd38dce5051dc3cb2a83cb5aaa1bfd26d52ec3ec-primary.sqlite.bz2 drwxr-xr-x 2 root root 31 Oct 24 18:37 gen drwxr-xr-x 2 root root 6 Oct 24 18:37 packages -rw-r--r-- 1 root root 2521 Oct 12 03:05 repomd.xml /var/cache/yum///x86_64/7/mysql-tools-community/gen: total 432 -rw-r--r-- 1 root root 440320 Oct 12 03:05 primary_db.sqlite /var/cache/yum///x86_64/7/mysql-tools-community/packages: total 0 [root@node101.yinzhengjie.org.cn ~]#
[root@node101.yinzhengjie.org.cn ~]# rpm -q --scripts mysql-community-server #我们发现MySQL的服务端脚本安装时会自动帮我们创建mysql用户 preinstall scriptlet (using /bin/sh): /usr/sbin/groupadd -g 27 -o -r mysql >/dev/null 2>&1 || : /usr/sbin/useradd -M -N -g mysql -o -r -d /var/lib/mysql -s /bin/false -c "MySQL Server" -u 27 mysql >/dev/null 2>&1 || : postinstall scriptlet (using /bin/sh): [ -e /var/log/mysqld.log ] || install -m0640 -omysql -gmysql /dev/null /var/log/mysqld.log >/dev/null 2>&1 || : if [ $1 -eq 1 ] ; then # Initial installation systemctl preset mysqld.service >/dev/null 2>&1 || : fi /usr/bin/systemctl enable mysqld >/dev/null 2>&1 || : preuninstall scriptlet (using /bin/sh): if [ $1 -eq 0 ] ; then # Package removal, not upgrade systemctl --no-reload disable mysqld.service > /dev/null 2>&1 || : systemctl stop mysqld.service > /dev/null 2>&1 || : fi postuninstall scriptlet (using /bin/sh): systemctl daemon-reload >/dev/null 2>&1 || : if [ $1 -ge 1 ] ; then # Package upgrade, not uninstall systemctl try-restart mysqld.service >/dev/null 2>&1 || : fi [root@node101.yinzhengjie.org.cn ~]#
3>.启动MySQL数据库
[root@node101.yinzhengjie.org.cn ~]# ll /var/lib/mysql #默认的MySQL数据存储目录为空 total 0 [root@node101.yinzhengjie.org.cn ~]# [root@node101.yinzhengjie.org.cn ~]# systemctl start mysqld #第一次启动MySQL数据库,观察默认的MySQL数据存储目录"/var/lib/mysql"是否有数据生成。 [root@node101.yinzhengjie.org.cn ~]# [root@node101.yinzhengjie.org.cn ~]# ll /var/lib/mysql #再次查看MySQL数据存储目录,发现多了很多初始文件和目录 total 168004 -rw-r----- 1 mysql mysql 56 Oct 24 20:34 auto.cnf -rw-r----- 1 mysql mysql 155 Oct 24 20:34 binlog.000001 -rw-r----- 1 mysql mysql 16 Oct 24 20:34 binlog.index -rw------- 1 mysql mysql 1676 Oct 24 20:34 ca-key.pem -rw-r--r-- 1 mysql mysql 1112 Oct 24 20:34 ca.pem -rw-r--r-- 1 mysql mysql 1112 Oct 24 20:34 client-cert.pem -rw------- 1 mysql mysql 1676 Oct 24 20:34 client-key.pem -rw-r----- 1 mysql mysql 5906 Oct 24 20:34 ib_buffer_pool -rw-r----- 1 mysql mysql 12582912 Oct 24 20:35 ibdata1 -rw-r----- 1 mysql mysql 50331648 Oct 24 20:35 ib_logfile0 -rw-r----- 1 mysql mysql 50331648 Oct 24 20:34 ib_logfile1 -rw-r----- 1 mysql mysql 12582912 Oct 24 20:35 ibtmp1 drwxr-x--- 2 mysql mysql 187 Oct 24 20:34 #innodb_temp drwxr-x--- 2 mysql mysql 143 Oct 24 20:34 mysql -rw-r----- 1 mysql mysql 25165824 Oct 24 20:35 mysql.ibd srwxrwxrwx 1 mysql mysql 0 Oct 24 20:34 mysql.sock -rw------- 1 mysql mysql 5 Oct 24 20:34 mysql.sock.lock drwxr-x--- 2 mysql mysql 8192 Oct 24 20:34 performance_schema -rw------- 1 mysql mysql 1680 Oct 24 20:34 private_key.pem -rw-r--r-- 1 mysql mysql 452 Oct 24 20:34 public_key.pem -rw-r--r-- 1 mysql mysql 1112 Oct 24 20:34 server-cert.pem -rw------- 1 mysql mysql 1680 Oct 24 20:34 server-key.pem drwxr-x--- 2 mysql mysql 28 Oct 24 20:34 sys -rw-r----- 1 mysql mysql 10485760 Oct 24 20:35 undo_001 -rw-r----- 1 mysql mysql 10485760 Oct 24 20:35 undo_002 [root@node101.yinzhengjie.org.cn ~]#
[root@node101.yinzhengjie.org.cn ~]# systemctl status mysqld #查看MySQL服务的状态。 ● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: active (running) since Thu 2019-10-24 20:34:58 CST; 6min ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 4021 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 4100 (mysqld) Status: "Server is operational" CGroup: /system.slice/mysqld.service └─4100 /usr/sbin/mysqld Oct 24 20:34:49 node101.yinzhengjie.org.cn systemd[1]: Starting MySQL Server... Oct 24 20:34:58 node101.yinzhengjie.org.cn systemd[1]: Started MySQL Server. [root@node101.yinzhengjie.org.cn ~]#
[root@node101.yinzhengjie.org.cn ~]# ps aux | grep mysqld | grep -v grep #查看MySQL服务的mysqld进程是否存在 mysql 4100 0.7 4.8 2041748 386572 ? Ssl Oct24 3:52 /usr/sbin/mysqld [root@node101.yinzhengjie.org.cn ~]# [root@node101.yinzhengjie.org.cn ~]# ss -ntl #我们发现默认的MySQL端口3306和33060已经启动成功了 State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:22 *:* LISTEN 0 128 :::3306 :::* LISTEN 0 128 :::22 :::* LISTEN 0 70 :::33060 :::* [root@node101.yinzhengjie.org.cn ~]#
[root@node101.yinzhengjie.org.cn ~]# yum -y install psmisc #安装pstree命令行工具 Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: mirrors.nju.edu.cn * extras: mirrors.nju.edu.cn * updates: mirrors.cn99.com Resolving Dependencies --> Running transaction check ---> Package psmisc.x86_64 0:22.20-15.el7 will be updated ---> Package psmisc.x86_64 0:22.20-16.el7 will be an update --> Finished Dependency Resolution Dependencies Resolved ================================================================================================================ Package Arch Version Repository Size ================================================================================================================ Updating: psmisc x86_64 22.20-16.el7 base 141 k Transaction Summary ================================================================================================================ Upgrade 1 Package Total download size: 141 k Downloading packages: Delta RPMs disabled because /usr/bin/applydeltarpm not installed. psmisc-22.20-16.el7.x86_64.rpm | 141 kB 00:00:00 Running transaction check Running transaction test Transaction test succeeded Running transaction Updating : psmisc-22.20-16.el7.x86_64 1/2 Cleanup : psmisc-22.20-15.el7.x86_64 2/2 Verifying : psmisc-22.20-16.el7.x86_64 1/2 Verifying : psmisc-22.20-15.el7.x86_64 2/2 Updated: psmisc.x86_64 0:22.20-16.el7 Complete! [root@node101.yinzhengjie.org.cn ~]#
[root@node101.yinzhengjie.org.cn ~]# ps aux | grep mysql | grep -v grep mysql 4100 0.6 4.8 2041748 386572 ? Ssl Oct24 3:54 /usr/sbin/mysqld [root@node101.yinzhengjie.org.cn ~]# [root@node101.yinzhengjie.org.cn ~]# pstree -p #注意观察MySQL服务的mysqld进程是由mysql用户启动的并运行了子线程。 systemd(1)─┬─NetworkManager(3065)─┬─dhclient(3126) │ ├─{NetworkManager}(3092) │ └─{NetworkManager}(3094) ├─agetty(3363) ├─atd(3083) ├─auditd(3029)───{auditd}(3030) ├─crond(3069) ├─dbus-daemon(3058) ├─irqbalance(3056) ├─lvmetad(1657) ├─mysqld(4100)─┬─{mysqld}(4106) │ ├─{mysqld}(4107) │ ├─{mysqld}(4108) │ ├─{mysqld}(4109) │ ├─{mysqld}(4110) │ ├─{mysqld}(4111) │ ├─{mysqld}(4112) │ ├─{mysqld}(4113) │ ├─{mysqld}(4114) │ ├─{mysqld}(4115) │ ├─{mysqld}(4116) │ ├─{mysqld}(4117) │ ├─{mysqld}(4118) │ ├─{mysqld}(4119) │ ├─{mysqld}(4120) │ ├─{mysqld}(4121) │ ├─{mysqld}(4122) │ ├─{mysqld}(4123) │ ├─{mysqld}(4124) │ ├─{mysqld}(4125) │ ├─{mysqld}(4126) │ ├─{mysqld}(4127) │ ├─{mysqld}(4128) │ ├─{mysqld}(4129) │ ├─{mysqld}(4130) │ ├─{mysqld}(4131) │ ├─{mysqld}(4133) │ ├─{mysqld}(4136) │ ├─{mysqld}(4137) │ ├─{mysqld}(4138) │ ├─{mysqld}(4139) │ ├─{mysqld}(4140) │ ├─{mysqld}(4141) │ ├─{mysqld}(4142) │ ├─{mysqld}(4143) │ ├─{mysqld}(4144) │ ├─{mysqld}(4145) │ └─{mysqld}(4156) ├─polkitd(3055)─┬─{polkitd}(3062) │ ├─{polkitd}(3063) │ ├─{polkitd}(3064) │ ├─{polkitd}(3066) │ ├─{polkitd}(3079) │ └─{polkitd}(3090) ├─rsyslogd(3342)─┬─{rsyslogd}(3356) │ └─{rsyslogd}(3361) ├─sshd(3345)─┬─sshd(3736)───bash(3757) │ ├─sshd(3799)───bash(3801)───pstree(4756) │ └─sshd(3874)───bash(3876) ├─systemd-journal(1637) ├─systemd-logind(3052) ├─systemd-udevd(1659) └─tuned(3340)─┬─{tuned}(3620) ├─{tuned}(3621) ├─{tuned}(3622) └─{tuned}(3635) [root@node101.yinzhengjie.org.cn ~]#
4>.查看mysql的启动日志获取MySQL数据库初始化密码
[root@node101.yinzhengjie.org.cn ~]# grep 'temporary password' /var/log/mysqld.log 2019-10-24T12:34:53.031896Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: 1ym9qd,pyULn #标红字符串时MySQL服务的初始化密码。 [root@node101.yinzhengjie.org.cn ~]#
5>.连接mysql服务器并修改密码
[root@node101.yinzhengjie.org.cn ~]# which mysql #查看MySQL的默认命令行客户端连接工具完整路径 /usr/bin/mysql [root@node101.yinzhengjie.org.cn ~]# [root@node101.yinzhengjie.org.cn ~]# ll /usr/bin/mysql -rwxr-xr-x 1 root root 19356128 Sep 20 17:23 /usr/bin/mysql [root@node101.yinzhengjie.org.cn ~]# [root@node101.yinzhengjie.org.cn ~]# rpm -qf /usr/bin/mysql #查看MySQL客户端命令mysql来自哪个rpm安装包 mysql-community-client-8.0.18-1.el7.x86_64 [root@node101.yinzhengjie.org.cn ~]# [root@node101.yinzhengjie.org.cn ~]#
[root@node101.yinzhengjie.org.cn ~]# mysql -uroot -p #使用上面查询到的MySQL初始化密码登录数据库,登录成功后会强制咱们修改MySQL的root用户密码 Enter password: #输入第4步重查询标红的字符串后登录成功 Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 9 Server version: 8.0.18 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> SHOW DATABASES; #发现我们无法执行SHOW命令,登录数据库后它强制咱们修改密码 ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. mysql> mysql> ALTER USER user() IDENTIFIED BY 'yinzhengjie'; #密码的复杂度要高,否则会提示咱们密码策略不符合要求,我们可以通过修改"validate_password.policy"的属性之来变更策略,但在此之前必须修修改复杂度较高的密码。 ERROR 1819 (HY000): Your password does not satisfy the current policy requirements mysql> mysql> ALTER USER user() IDENTIFIED BY 'yzj@2019MySQL'; #我们按要求修改符合安全策略的密码 Query OK, 0 rows affected (0.01 sec) mysql> mysql> EXIT Bye [root@node101.yinzhengjie.org.cn ~]#
[root@node101.yinzhengjie.org.cn ~]# mysql -uroot -pyzj@2019MySQL #使用修改后的密码登录数据库并修改默认的密码安全级别 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 10 Server version: 8.0.18 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> SHOW VARIABLES LIKE '%validate_password_policy%'; #查看MySQL默认设置的密码安全级别为"MEDIUM"(除了长度要求外,该级别要求密码中必须包含数字,大写/小写字母和特殊字符) +--------------------------+--------+ | Variable_name | Value | +--------------------------+--------+ | validate_password.policy | MEDIUM | +--------------------------+--------+ row in set (0.00 sec) mysql> mysql> SET GLOBAL validate_password.policy=0; #修改MySQL默认的安全级别为LOW,这样咱们就可以设置相对简单的密码啦,便于咱们测试。 Query OK, 0 rows affected (0.00 sec) mysql> mysql> SHOW VARIABLES LIKE '%validate_password_policy%'; #再次查看MySQL默认安全策略,发现其值发生了变化。 +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | validate_password.policy | LOW | +--------------------------+-------+ row in set (0.01 sec) mysql> mysql> ALTER USER user() IDENTIFIED BY 'yinzhengjie'; #与此同时,我们可以设置安全策略较低的密码。 Query OK, 0 rows affected (0.01 sec) mysql> mysql> QUIT Bye [root@node101.yinzhengjie.org.cn ~]# [root@node101.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie #使用修改后的简单密码进行登录 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 12 Server version: 8.0.18 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> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql>
validate_password_policy各项值说明:(密码安全策略,默认MEDIUM策略) 0 or LOW: Length; 1 or MEDIUM(默认): Length;numeric, lowercase/uppercase, and special characters; 2 or STRONG: Length; numeric, lowercase/uppercase, and special characters; dictionary file
[root@node101.yinzhengjie.org.cn ~]# grep socket /etc/my.cnf #默认的本地socket连接文件 socket=/var/lib/mysql/mysql.sock [root@node101.yinzhengjie.org.cn ~]# [root@node101.yinzhengjie.org.cn ~]# mysql -uroot -p -S /var/lib/mysql/mysql.sock #使用"-S"指定默认的本地socket连接文件,默认路径在"/var/lib/mysql/mysql.sock",如果没有修改默认路径咱们可以不指定。 Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 13 Server version: 8.0.18 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> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql>
6>.MySQL客户端命令
mysql客户端常用选项: -A, --no-auto-rehash 禁止补全 -u, --user= 用户名,默认为root -h, --host= 服务器主机,默认为localhost -p, --passowrd= 用户密码,建议使用-p,默认为空密码 -P, --port= 服务器端口 -S, --socket= 指定连接socket文件路径 -D, --database= 指定默认数据库 -C, --compress 启用压缩 -e “SQL“ 执行SQL命令 -V, --version 显示版本 -v --verbose 显示详细信息 --print-defaults 获取程序默认使用的配置
[root@node101.yinzhengjie.org.cn ~]# mysql --help #查看mysql命令的帮助信息 mysql Ver 8.0.18 for Linux on x86_64 (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. Usage: mysql [OPTIONS] [database] -?, --help Display this help and exit. -I, --help Synonym for -? --auto-rehash Enable automatic rehashing. One doesn't need to use 'rehash' to get table and field completion, but startup and reconnecting may take a longer time. Disable with --disable-auto-rehash. (Defaults to on; use --skip-auto-rehash to disable.) -A, --no-auto-rehash No automatic rehashing. One has to use 'rehash' to get table and field completion. This gives a quicker start of mysql and disables rehashing on reconnect. --auto-vertical-output Automatically switch to vertical output mode if the result is wider than the terminal width. -B, --batch Don't use history file. Disable interactive behavior. (Enables --silent.) --bind-address=name IP address to bind to. --binary-as-hex Print binary data as hex --character-sets-dir=name Directory for character set files. --column-type-info Display column type information. -c, --comments Preserve comments. Send comments to the server. The default is --skip-comments (discard comments), enable with --comments. -C, --compress Use compression in server/client protocol. -#, --debug[=#] This is a non-debug version. Catch this and exit. --debug-check This is a non-debug version. Catch this and exit. -T, --debug-info This is a non-debug version. Catch this and exit. -D, --database=name Database to use. --default-character-set=name Set the default character set. --delimiter=name Delimiter to be used. --enable-cleartext-plugin Enable/disable the clear text authentication plugin. -e, --execute=name Execute command and quit. (Disables --force and history file.) -E, --vertical Print the output of a query (rows) vertically. -f, --force Continue even if we get an SQL error. --histignore=name A colon-separated list of patterns to keep statements from getting logged into syslog and mysql history. -G, --named-commands Enable named commands. Named commands mean this program's internal commands; see mysql> help . When enabled, the named commands can be used from any line of the query, otherwise only from the first line, before an enter. Disable with --disable-named-commands. This option is disabled by default. -i, --ignore-spaces Ignore space after function names. --init-command=name SQL Command to execute when connecting to MySQL server. Will automatically be re-executed when reconnecting. --local-infile Enable/disable LOAD DATA LOCAL INFILE. -b, --no-beep Turn off beep on error. -h, --host=name Connect to host. -H, --html Produce HTML output. -X, --xml Produce XML output. --line-numbers Write line numbers for errors. (Defaults to on; use --skip-line-numbers to disable.) -L, --skip-line-numbers Don't write line number for errors. -n, --unbuffered Flush buffer after each query. --column-names Write column names in results. (Defaults to on; use --skip-column-names to disable.) -N, --skip-column-names Don't write column names in results. --sigint-ignore Ignore SIGINT (CTRL-C). -o, --one-database Ignore statements except those that occur while the default database is the one named at the command line. --pager[=name] Pager to use to display results. If you don't supply an option, the default pager is taken from your ENV variable PAGER. Valid pagers are less, more, cat [> filename], etc. See interactive help (h) also. This option does not work in batch mode. Disable with --disable-pager. This option is disabled by default. -p, --password[=name] Password to use when connecting to server. If password is not given it's asked from the tty. -P, --port=# Port number to use for connection or 0 for default to, in order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default (3306). --prompt=name Set the mysql prompt to this value. --protocol=name The protocol to use for connection (tcp, socket, pipe, memory). -q, --quick Don't cache result, print it row by row. This may slow down the server if the output is suspended. Doesn't use history file. -r, --raw Write fields without conversion. Used with --batch. --reconnect Reconnect if the connection is lost. Disable with --disable-reconnect. This option is enabled by default. (Defaults to on; use --skip-reconnect to disable.) -s, --silent Be more silent. Print results with a tab as separator, each row on new line. -S, --socket=name The socket file to use for connection. --server-public-key-path=name File path to the server public RSA key in PEM format. --get-server-public-key Get server public key --ssl-mode=name SSL connection mode. --ssl-ca=name CA file in PEM format. --ssl-capath=name CA directory. --ssl-cert=name X509 cert in PEM format. --ssl-cipher=name SSL cipher to use. --ssl-key=name X509 key in PEM format. --ssl-crl=name Certificate revocation list. --ssl-crlpath=name Certificate revocation list path. --tls-version=name TLS version to use, permitted values are: TLSv1, TLSv1.1, TLSv1.2, TLSv1.3 --ssl-fips-mode=name SSL FIPS mode (applies only for OpenSSL); permitted values are: OFF, ON, STRICT --tls-ciphersuites=name TLS v1.3 cipher to use. -t, --table Output in table format. --tee=name Append everything into outfile. See interactive help (h) also. Does not work in batch mode. Disable with --disable-tee. This option is disabled by default. -u, --user=name User for login if not current user. -U, --safe-updates Only allow UPDATE and DELETE that uses keys. -U, --i-am-a-dummy Synonym for option --safe-updates, -U. -v, --verbose Write more. (-v -v -v gives the table output format). -V, --version Output version information and exit. -w, --wait Wait and retry if connection is down. --connect-timeout=# Number of seconds before connection timeout. --max-allowed-packet=# The maximum packet length to send to or receive from server. --net-buffer-length=# The buffer size for TCP/IP and socket communication. --select-limit=# Automatic limit for SELECT when using --safe-updates. --max-join-size=# Automatic limit for rows in a join when using --safe-updates. --show-warnings Show warnings after every statement. -j, --syslog Log filtered interactive commands to syslog. Filtering of commands depends on the patterns supplied via histignore option besides the default patterns. --plugin-dir=name Directory for client-side plugins. --default-auth=name Default authentication client-side plugin to use. --binary-mode By default, ASCII '0' is disallowed and ' ' is translated to ' '. This switch turns off both features, and also turns off parsing of all clientcommands except C and DELIMITER, in non-interactive mode (for input piped to mysql or loaded using the 'source' command). This is necessary when processing output from mysqlbinlog that may contain blobs. --connect-expired-password Notify the server that this client is prepared to handle expired password sandbox mode. --network-namespace=name Network namespace to use for connection via tcp with a server. --compression-algorithms=name Use compression algorithm in server/client protocol. Valid values are any combination of 'zstd','zlib','uncompressed'. --zstd-compression-level=# Use this compression level in the client/server protocol, in case --compression-algorithms=zstd. Valid range is between 1 and 22, inclusive. Default is 3. Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf The following groups are read: mysql client The following options may be given as the first argument: --print-defaults Print the program argument list and exit. --no-defaults Don't read default options from any option file, except for login file. --defaults-file=# Only read default options from the given file #. --defaults-extra-file=# Read this file after the global files are read. --defaults-group-suffix=# Also read groups with concat(group, suffix) --login-path=# Read this path from the login file. Variables (--variable-name=value) and boolean options {FALSE|TRUE} Value (after reading options) --------------------------------- ---------------------------------------- auto-rehash TRUE auto-vertical-output FALSE bind-address (No default value) binary-as-hex FALSE character-sets-dir (No default value) column-type-info FALSE comments FALSE compress FALSE database (No default value) default-character-set auto delimiter ; enable-cleartext-plugin FALSE vertical FALSE force FALSE histignore (No default value) named-commands FALSE ignore-spaces FALSE init-command (No default value) local-infile FALSE no-beep FALSE host (No default value) html FALSE xml FALSE line-numbers TRUE unbuffered FALSE column-names TRUE sigint-ignore FALSE port 0 prompt mysql> quick FALSE raw FALSE reconnect TRUE socket (No default value) server-public-key-path (No default value) get-server-public-key FALSE ssl-ca (No default value) ssl-capath (No default value) ssl-cert (No default value) ssl-cipher (No default value) ssl-key (No default value) ssl-crl (No default value) ssl-crlpath (No default value) tls-version (No default value) tls-ciphersuites (No default value) table FALSE user (No default value) safe-updates FALSE i-am-a-dummy FALSE connect-timeout 0 max-allowed-packet 16777216 net-buffer-length 16384 select-limit 1000 max-join-size 1000000 show-warnings FALSE plugin-dir (No default value) default-auth (No default value) binary-mode FALSE connect-expired-password FALSE network-namespace (No default value) compression-algorithms (No default value) zstd-compression-level 3 [root@node101.yinzhengjie.org.cn ~]#
[root@node101.yinzhengjie.org.cn ~]# man mysql #共计有1492行帮助信息,相对上面的帮助信息更全面
[root@node101.yinzhengjie.org.cn ~]# mysql -uroot -p -S /var/lib/mysql/mysql.sock Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 14 Server version: 8.0.18 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> help; #查看MySQL客户端(mysql命令行工具)专有命令,因此我们使用其它MySQL客户端工具连接MySQL服务端(mysqld)可能没有这些命令,与之对应的还有MySQL服务端命令。 For information about MySQL products and services, visit: http://www.mysql.com/ For developer information, including the MySQL Reference Manual, visit: http://dev.mysql.com/ To buy MySQL Enterprise support, training, or other products, visit: https://shop.mysql.com/ List of all MySQL commands: Note that all text commands must be first on line and end with ';' ? (?) Synonym for `help'. clear (c) Clear the current input statement. connect ( ) Reconnect to the server. Optional arguments are db and host. delimiter (d) Set statement delimiter. edit (e) Edit command with $EDITOR. ego (G) Send command to mysql server, display result vertically. exit (q) Exit mysql. Same as quit. go (g) Send command to mysql server. help (h) Display this help. nopager ( ) Disable pager, print to stdout. notee ( ) Don't write into outfile. pager (P) Set PAGER [to_pager]. Print the query results via PAGER. print (p) Print current command. prompt (R) Change your mysql prompt. quit (q) Quit mysql. rehash (#) Rebuild completion hash. source (.) Execute an SQL script file. Takes a file name as an argument. status (s) Get status information from the server. system (!) Execute a system shell command. tee (T) Set outfile [to_outfile]. Append everything into given outfile. use (u) Use another database. Takes database name as argument. charset (C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets. warnings (W) Show warnings after every statement. nowarning (w) Don't show warnings after every statement. resetconnection(x) Clean session context. For server side help, type 'help contents' mysql>
mysql> STATUS #从服务器获取状态信息。 -------------- mysql Ver 8.0.18 for Linux on x86_64 (MySQL Community Server - GPL) Connection id: 14 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 8.0.18 MySQL Community Server - GPL Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 10 hours 26 min 53 sec Threads: 2 Questions: 28 Slow queries: 0 Opens: 177 Flush tables: 3 Open tables: 94 Queries per second avg: 0.000 -------------- mysql>
mysql> SYSTEM hostname #在mysql命令行调用Linux命令 node101.yinzhengjie.org.cn mysql>
mysql> prompt node101.yinzhengjie.org.cn[mysql]--> #临时修改当前命令行的提示符信息 PROMPT set to 'node101.yinzhengjie.org.cn[mysql]-->' node101.yinzhengjie.org.cn[mysql]--> node101.yinzhengjie.org.cn[mysql]-->system free -h total used free shared buff/cache available Mem: 7.6G 509M 4.0G 8.6M 3.1G 6.8G Swap: 7.9G 0B 7.9G node101.yinzhengjie.org.cn[mysql]--> node101.yinzhengjie.org.cn[mysql]-->
[root@node101.yinzhengjie.org.cn ~]# mysql -uroot -p --prompt="u@[D]-->" #启动mysql客户端连接时临时指定提示符信息 Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 16 Server version: 8.0.18 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. root@[Fri Oct 25 07:36:43 2019]--> root@[Fri Oct 25 07:36:44 2019]-->SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) root@[Fri Oct 25 07:36:48 2019]-->QUIT Bye [root@node101.yinzhengjie.org.cn ~]#
[root@node101.yinzhengjie.org.cn ~]# tail -2 /etc/my.cnf #将MySQL客户端的配置写入配置文件,这些配置就会永久生效。 [mysql] prompt="u@[D]->" [root@node101.yinzhengjie.org.cn ~]# [root@node101.yinzhengjie.org.cn ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 18 Server version: 8.0.18 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. root@[Fri Oct 25 07:45:36 2019]-> root@[Fri Oct 25 07:45:37 2019]->SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) root@[Fri Oct 25 07:45:41 2019]-> root@[Fri Oct 25 07:45:42 2019]->QUIT Bye [root@node101.yinzhengjie.org.cn ~]# [root@node101.yinzhengjie.org.cn ~]#
7>.运行MySQL/MariaDB自带的安全脚本(提高安全性)
[root@node102.yinzhengjie.org.cn ~]# mysql #默认情况下MariaDB无需输入密码即可登录数据库,查看数据库及用户信息 Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 16 Server version: 5.5.64-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)]> MariaDB [(none)]> SHOW DATABASES; #查看现有的数据库,存在test数据库。 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ rows in set (0.00 sec) MariaDB [(none)]> MariaDB [mysql]> SELECT host,user,password FROM user; #查看默认的mysql服务用户信息,发现有很多匿名用户且密码为空。 +----------------------------+------+----------+ | host | user | password | +----------------------------+------+----------+ | localhost | root | | | node102.yinzhengjie.org.cn | root | | | 127.0.0.1 | root | | | ::1 | root | | | localhost | | | | node102.yinzhengjie.org.cn | | | +----------------------------+------+----------+ rows in set (0.00 sec) MariaDB [mysql]>
[root@node102.yinzhengjie.org.cn ~]# mysql_secure_installation #启动MySQL/MaraDB默认的安全策略脚本,根据提示输入对应的选项即可。 NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, 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): #输入MySQL/MariaDB的默认密码,如果没有设置密码直接回车即可。 OK, successfully used password, moving on... Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorisation. Set root password? [Y/n] y #输入"y",表示设置root密码 New password: Re-enter new password: Password updated successfully! Reloading privilege tables.. ... Success! By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB 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] y #输入"y",移除匿名用户 ... 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] y #输入"y",禁用root远程登录 ... Success! By default, MariaDB 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] y #输入"y",移除"test"数据库 - 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] y #输入"y",使得配置立即生效 ... Success! Cleaning up... All done! If you've completed all of the above steps, your MariaDB installation should now be secure. Thanks for using MariaDB! [root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# mysql -p #需要使用使用脚本设置的密码登录,再次查看数据库及用户信息。 Enter password: Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 29 Server version: 5.5.64-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)]> MariaDB [(none)]> SHOW DATABASES; #发现"test"数据库不存在啦 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> SELECT host,user,password FROM mysql.user; #发现匿名用户被删除了,目前仅允许本机登录。 +-----------+------+-------------------------------------------+ | host | user | password | +-----------+------+-------------------------------------------+ | localhost | root | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | 127.0.0.1 | root | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | ::1 | root | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | +-----------+------+-------------------------------------------+ 3 rows in set (0.00 sec) MariaDB [(none)]>
8>.用户账号
mysql用户账号由两部分组成: 'USERNAME'@'HOST‘ 说明: HOST限制此用户可通过哪些远程主机连接mysql服务器 支持使用通配符: % 匹配任意长度的任意字符 172.30.1.0/255.255.255.0 或 172.30.1.% _ 匹配任意单个字符
9>.Mysql 客户端使用模式
交互式模式: 可运行命令有两类: 客户端命令: h, help u,use s,status !,system 服务器端命令: SQL语句, 需要语句结束符;
脚本模式: mysql –uUSERNAME -pPASSWORD < /path/somefile.sql mysql> source /path/from/somefile.sql
[root@node102.yinzhengjie.org.cn ~]# cat test.sql SELECT NOW(); SHOW DATABASES; SELECT host,user,password FROM mysql.user; [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie < test.sql #批量执行一个SQL脚本,这样就省的我们人工一条一条敲了。 NOW() 2019-10-25 10:09:18 Database information_schema mysql performance_schema host user password localhost root *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 127.0.0.1 root *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 ::1 root *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# cat test.sql | mysql -uroot -pyinzhengjie #上面的方法也等效于这种写法的结果 NOW() 2019-10-25 10:10:06 Database information_schema mysql performance_schema host user password localhost root *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 127.0.0.1 root *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 ::1 root *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -e 'SELECT host,user,password FROM mysql.user' #命令行使用非交互式方式执行SQL语句 +-----------+------+-------------------------------------------+ | host | user | password | +-----------+------+-------------------------------------------+ | localhost | root | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | 127.0.0.1 | root | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | ::1 | root | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | +-----------+------+-------------------------------------------+ [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie -e 'SHOW DATABASES;' +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ [root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# cat test.sql SELECT NOW(); SHOW DATABASES; SELECT host,user,password FROM mysql.user; [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# mysql -uroot -pyinzhengjie Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 10 Server version: 5.5.64-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)]> MariaDB [(none)]> SOURCE test.sql #交互式命令行中执行SQL脚本 +---------------------+ | NOW() | +---------------------+ | 2019-10-25 10:14:53 | +---------------------+ 1 row in set (0.00 sec) +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec) +-----------+------+-------------------------------------------+ | host | user | password | +-----------+------+-------------------------------------------+ | localhost | root | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | 127.0.0.1 | root | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | | ::1 | root | *BD0B1F48FDC55BD27555FC2F22FF29A68A25A1D7 | +-----------+------+-------------------------------------------+ 3 rows in set (0.00 sec) MariaDB [(none)]>
10>.服务器监听的两种socket地址
ip socket:
监听在tcp的3306端口,支持远程通信
unix sock:
监听在sock文件上,仅支持本机通信。如:/var/lib/mysql/mysql.sock)
说明:host为localhost,127.0.0.1时自动使用unix sock
[root@node102.yinzhengjie.org.cn ~]# ll /var/lib/mysql/mysql.sock #默认的本地socket连接文件 srwxrwxrwx 1 mysql mysql 0 Oct 25 06:26 /var/lib/mysql/mysql.sock [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# mysql -S /var/lib/mysql/mysql.sock #使用"-S"指定默认的本地socket连接文件,默认路径在"/var/lib/mysql/mysql.sock",如果没有修改默认路径咱们可以不指定。 Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 6 Server version: 5.5.64-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)]> help General information about MariaDB can be found at http://mariadb.org List of all MySQL commands: Note that all text commands must be first on line and end with ';' ? (?) Synonym for `help'. clear (c) Clear the current input statement. connect ( ) Reconnect to the server. Optional arguments are db and host. delimiter (d) Set statement delimiter. edit (e) Edit command with $EDITOR. ego (G) Send command to mysql server, display result vertically. exit (q) Exit mysql. Same as quit. go (g) Send command to mysql server. help (h) Display this help. nopager ( ) Disable pager, print to stdout. notee ( ) Don't write into outfile. pager (P) Set PAGER [to_pager]. Print the query results via PAGER. print (p) Print current command. prompt (R) Change your mysql prompt. quit (q) Quit mysql. rehash (#) Rebuild completion hash. source (.) Execute an SQL script file. Takes a file name as an argument. status (s) Get status information from the server. system (!) Execute a system shell command. tee (T) Set outfile [to_outfile]. Append everything into given outfile. use (u) Use another database. Takes database name as argument. charset (C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets. warnings (W) Show warnings after every statement. nowarning (w) Don't show warnings after every statement. For server side help, type 'help contents' MariaDB [(none)]> EXIT Bye [root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# ll /var/lib/mysql/ total 28700 -rw-rw---- 1 mysql mysql 16384 Oct 25 09:57 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 Oct 25 09:57 aria_log_control -rw-rw---- 1 mysql mysql 18874368 Oct 25 09:57 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Oct 25 10:07 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Oct 25 06:26 ib_logfile1 drwx------ 2 mysql mysql 4096 Oct 25 06:26 mysql srwxrwxrwx 1 mysql mysql 0 Oct 25 10:07 mysql.sock drwx------ 2 mysql mysql 4096 Oct 25 06:26 performance_schema [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# mysqladmin -uroot -pyinzhengjie shutdown #关闭MySQL/MariaDB服务,观察"mysql.sock"是否自动消失。 [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# ll /var/lib/mysql/ total 28700 -rw-rw---- 1 mysql mysql 16384 Oct 25 10:24 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 Oct 25 10:24 aria_log_control -rw-rw---- 1 mysql mysql 18874368 Oct 25 10:24 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Oct 25 10:24 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Oct 25 06:26 ib_logfile1 drwx------ 2 mysql mysql 4096 Oct 25 06:26 mysql drwx------ 2 mysql mysql 4096 Oct 25 06:26 performance_schema [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# systemctl start mariadb [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# ll /var/lib/mysql/ total 28700 -rw-rw---- 1 mysql mysql 16384 Oct 25 10:24 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 Oct 25 10:24 aria_log_control -rw-rw---- 1 mysql mysql 18874368 Oct 25 10:24 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Oct 25 10:24 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Oct 25 06:26 ib_logfile1 drwx------ 2 mysql mysql 4096 Oct 25 06:26 mysql srwxrwxrwx 1 mysql mysql 0 Oct 25 10:24 mysql.sock drwx------ 2 mysql mysql 4096 Oct 25 06:26 performance_schema [root@node102.yinzhengjie.org.cn ~]#
11>.执行命令
运行mysql命令:默认空密码登录 mysql>use mysql mysql>select user();查看当前用户 mysql>SELECT User,Host,Password FROM user;
登录系统:
mysql –uroot –p
客户端命令:本地执行 mysql> help 每个命令都完整形式和简写格式 mysql> status 或 s
服务端命令:通过mysql协议发往服务器执行并取回结果 每个命令末尾都必须使用命令结束符号,默认为分号 示例:SELECT VERSION();
12>.服务器端配置
服务器端(mysqld):工作特性有多种配置方式 1>.命令行选项: 2>.配置文件:类ini格式 集中式的配置,能够为mysql的各应用程序提供配置信息: [mysqld]
针对MySQL主程序mysqld定义参数。 [mysqld_safe]
针对MySQL安全性的参数 [mysqld_multi]
针对MySQL多实例参数。 [mysql]
针对MySQL客户端参数。 [mysqldump]
针对MySQL备份相关参数。 [server]
统一为服务器使用。 [client]
统一为客户端使用。 格式: parameter = value 说明: _和- 相同 1,ON,TRUE意义相同, 0,OFF,FALSE意义相同
13>.配置文件
后面覆盖前面的配置文件,顺序如下: /etc/my.cnf Global选项 /etc/mysql/my.cnf Global选项 SYSCONFDIR/my.cnf Global选项(编译选项) $MYSQL_HOME/my.cnf Server-specific 选项 --defaults-extra-file=path (启动MySQL时使用该选项指定配置文件) ~/.my.cnf User-specific 选项
注意:以上顺序自上而下可同时指定多个配置文件,但谁在最下面谁的配置文件优先级最高,即后面的会覆盖前面的配置,类似于编程语言变量的定义。
三.基于yum方式安装Mariadb
基于默认的yum源时可以安装的MariaDB,但版本较低,可以基于yum方式安装MySQL一样去MariaDB官网找到对应版本的yum源下载安装即可。
官网地址:https://downloads.mariadb.org/mariadb/repositories/#mirror=iweb
1>.CentOS 7.6安装Mariadb
[root@node102.yinzhengjie.org.cn ~]# cat /etc/redhat-release CentOS Linux release 7.6.1810 (Core) [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# uname -r 3.10.0-957.el7.x86_64 [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# uname -m x86_64 [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# yum info mariadb #查看Mariadb数据库的相关信息 Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: mirrors.huaweicloud.com * extras: mirrors.cn99.com * updates: mirrors.cn99.com Available Packages Name : mariadb Arch : x86_64 Epoch : 1 Version : 5.5.64 Release : 1.el7 Size : 8.7 M Repo : base/7/x86_64 Summary : A community developed branch of MySQL URL : http://mariadb.org License : GPLv2 with exceptions and LGPLv2 and BSD Description : MariaDB is a community developed branch of MySQL. : MariaDB is a multi-user, multi-threaded SQL database server. : It is a client/server implementation consisting of a server daemon (mysqld) : and many different client programs and libraries. The base package : contains the standard MariaDB/MySQL client programs and generic MySQL files. [root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# yum -y install mariadb-server #安装mariadb服务端时,它会自动安装客户端 Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: mirrors.huaweicloud.com * extras: mirrors.cn99.com * updates: mirrors.cn99.com Resolving Dependencies --> Running transaction check ---> Package mariadb-server.x86_64 1:5.5.64-1.el7 will be installed --> Processing Dependency: mariadb(x86-64) = 1:5.5.64-1.el7 for package: 1:mariadb-server-5.5.64-1.el7.x86_64 --> Processing Dependency: perl-DBI for package: 1:mariadb-server-5.5.64-1.el7.x86_64 --> Processing Dependency: perl-DBD-MySQL for package: 1:mariadb-server-5.5.64-1.el7.x86_64 --> Processing Dependency: perl(DBI) for package: 1:mariadb-server-5.5.64-1.el7.x86_64 --> Running transaction check ---> Package mariadb.x86_64 1:5.5.64-1.el7 will be installed ---> Package perl-DBD-MySQL.x86_64 0:4.023-6.el7 will be installed ---> Package perl-DBI.x86_64 0:1.627-4.el7 will be installed --> Processing Dependency: perl(RPC::PlServer) >= 0.2001 for package: perl-DBI-1.627-4.el7.x86_64 --> Processing Dependency: perl(RPC::PlClient) >= 0.2000 for package: perl-DBI-1.627-4.el7.x86_64 --> Running transaction check ---> Package perl-PlRPC.noarch 0:0.2020-14.el7 will be installed --> Processing Dependency: perl(Net::Daemon) >= 0.13 for package: perl-PlRPC-0.2020-14.el7.noarch --> Processing Dependency: perl(Net::Daemon::Test) for package: perl-PlRPC-0.2020-14.el7.noarch --> Processing Dependency: perl(Net::Daemon::Log) for package: perl-PlRPC-0.2020-14.el7.noarch --> Running transaction check ---> Package perl-Net-Daemon.noarch 0:0.48-5.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================================================================================= Package Arch Version Repository Size ================================================================================================================================================= Installing: mariadb-server x86_64 1:5.5.64-1.el7 base 11 M Installing for dependencies: mariadb x86_64 1:5.5.64-1.el7 base 8.7 M perl-DBD-MySQL x86_64 4.023-6.el7 base 140 k perl-DBI x86_64 1.627-4.el7 base 802 k perl-Net-Daemon noarch 0.48-5.el7 base 51 k perl-PlRPC noarch 0.2020-14.el7 base 36 k Transaction Summary ================================================================================================================================================= Install 1 Package (+5 Dependent packages) Total size: 21 M Total download size: 20 M Installed size: 109 M Downloading packages: Delta RPMs disabled because /usr/bin/applydeltarpm not installed. (1/2): mariadb-5.5.64-1.el7.x86_64.rpm | 8.7 MB 00:00:01 (2/2): mariadb-server-5.5.64-1.el7.x86_64.rpm | 11 MB 00:00:06 ------------------------------------------------------------------------------------------------------------------------------------------------- Total 2.9 MB/s | 20 MB 00:00:06 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : 1:mariadb-5.5.64-1.el7.x86_64 1/6 Installing : perl-Net-Daemon-0.48-5.el7.noarch 2/6 Installing : perl-PlRPC-0.2020-14.el7.noarch 3/6 Installing : perl-DBI-1.627-4.el7.x86_64 4/6 Installing : perl-DBD-MySQL-4.023-6.el7.x86_64 5/6 Installing : 1:mariadb-server-5.5.64-1.el7.x86_64 6/6 Verifying : perl-Net-Daemon-0.48-5.el7.noarch 1/6 Verifying : 1:mariadb-5.5.64-1.el7.x86_64 2/6 Verifying : perl-DBD-MySQL-4.023-6.el7.x86_64 3/6 Verifying : 1:mariadb-server-5.5.64-1.el7.x86_64 4/6 Verifying : perl-DBI-1.627-4.el7.x86_64 5/6 Verifying : perl-PlRPC-0.2020-14.el7.noarch 6/6 Installed: mariadb-server.x86_64 1:5.5.64-1.el7 Dependency Installed: mariadb.x86_64 1:5.5.64-1.el7 perl-DBD-MySQL.x86_64 0:4.023-6.el7 perl-DBI.x86_64 0:1.627-4.el7 perl-Net-Daemon.noarch 0:0.48-5.el7 perl-PlRPC.noarch 0:0.2020-14.el7 Complete! [root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# rpm -ql mariadb-server #查看安装包中带有的文件列表 /etc/logrotate.d/mariadb /etc/my.cnf.d/server.cnf /usr/bin/innochecksum /usr/bin/myisam_ftdump /usr/bin/myisamchk /usr/bin/myisamlog /usr/bin/myisampack /usr/bin/mysql_convert_table_format /usr/bin/mysql_fix_extensions /usr/bin/mysql_install_db /usr/bin/mysql_plugin /usr/bin/mysql_secure_installation /usr/bin/mysql_setpermission /usr/bin/mysql_tzinfo_to_sql /usr/bin/mysql_upgrade /usr/bin/mysql_zap /usr/bin/mysqlbug /usr/bin/mysqld_multi /usr/bin/mysqld_safe /usr/bin/mysqld_safe_helper /usr/bin/mysqldumpslow /usr/bin/mysqlhotcopy /usr/bin/mysqltest /usr/bin/perror /usr/bin/replace /usr/bin/resolve_stack_dump /usr/bin/resolveip /usr/lib/systemd/system/mariadb.service /usr/lib/tmpfiles.d/mariadb.conf /usr/lib64/mysql/INFO_BIN /usr/lib64/mysql/INFO_SRC /usr/lib64/mysql/mysqlbug /usr/lib64/mysql/plugin /usr/lib64/mysql/plugin/adt_null.so /usr/lib64/mysql/plugin/auth_0x0100.so /usr/lib64/mysql/plugin/auth_pam.so /usr/lib64/mysql/plugin/auth_socket.so /usr/lib64/mysql/plugin/auth_test_plugin.so /usr/lib64/mysql/plugin/daemon_example.ini /usr/lib64/mysql/plugin/dialog_examples.so /usr/lib64/mysql/plugin/ha_innodb.so /usr/lib64/mysql/plugin/ha_sphinx.so /usr/lib64/mysql/plugin/handlersocket.so /usr/lib64/mysql/plugin/libdaemon_example.so /usr/lib64/mysql/plugin/mypluglib.so /usr/lib64/mysql/plugin/qa_auth_client.so /usr/lib64/mysql/plugin/qa_auth_interface.so /usr/lib64/mysql/plugin/qa_auth_server.so /usr/lib64/mysql/plugin/query_cache_info.so /usr/lib64/mysql/plugin/semisync_master.so /usr/lib64/mysql/plugin/semisync_slave.so /usr/lib64/mysql/plugin/server_audit.so /usr/lib64/mysql/plugin/sphinx.so /usr/lib64/mysql/plugin/sql_errlog.so /usr/libexec/mariadb-prepare-db-dir /usr/libexec/mariadb-wait-ready /usr/libexec/mysqld /usr/share/man/man1/innochecksum.1.gz /usr/share/man/man1/msql2mysql.1.gz /usr/share/man/man1/myisam_ftdump.1.gz /usr/share/man/man1/myisamchk.1.gz /usr/share/man/man1/myisamlog.1.gz /usr/share/man/man1/myisampack.1.gz /usr/share/man/man1/mysql.server.1.gz /usr/share/man/man1/mysql_convert_table_format.1.gz /usr/share/man/man1/mysql_fix_extensions.1.gz /usr/share/man/man1/mysql_install_db.1.gz /usr/share/man/man1/mysql_plugin.1.gz /usr/share/man/man1/mysql_secure_installation.1.gz /usr/share/man/man1/mysql_setpermission.1.gz /usr/share/man/man1/mysql_tzinfo_to_sql.1.gz /usr/share/man/man1/mysql_upgrade.1.gz /usr/share/man/man1/mysql_zap.1.gz /usr/share/man/man1/mysqlbinlog.1.gz /usr/share/man/man1/mysqlbug.1.gz /usr/share/man/man1/mysqlcheck.1.gz /usr/share/man/man1/mysqld_multi.1.gz /usr/share/man/man1/mysqld_safe.1.gz /usr/share/man/man1/mysqldumpslow.1.gz /usr/share/man/man1/mysqlhotcopy.1.gz /usr/share/man/man1/mysqlimport.1.gz /usr/share/man/man1/mysqltest.1.gz /usr/share/man/man1/perror.1.gz /usr/share/man/man1/replace.1.gz /usr/share/man/man1/resolve_stack_dump.1.gz /usr/share/man/man1/resolveip.1.gz /usr/share/man/man8/mysqld.8.gz /usr/share/mysql/README.mysql-cnf /usr/share/mysql/errmsg-utf8.txt /usr/share/mysql/fill_help_tables.sql /usr/share/mysql/my-huge.cnf /usr/share/mysql/my-innodb-heavy-4G.cnf /usr/share/mysql/my-large.cnf /usr/share/mysql/my-medium.cnf /usr/share/mysql/my-small.cnf /usr/share/mysql/mysql_performance_tables.sql /usr/share/mysql/mysql_system_tables.sql /usr/share/mysql/mysql_system_tables_data.sql /usr/share/mysql/mysql_test_data_timezone.sql /var/lib/mysql /var/log/mariadb /var/log/mariadb/mariadb.log /var/run/mariadb [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# rpm -q --scripts mariadb-server #安装Mariadb服务端会自动创建mysql用户 preinstall scriptlet (using /bin/sh): /usr/sbin/groupadd -g 27 -o -r mysql >/dev/null 2>&1 || : /usr/sbin/useradd -M -N -g mysql -o -r -d /var/lib/mysql -s /sbin/nologin -c "MariaDB Server" -u 27 mysql >/dev/null 2>&1 || : postinstall scriptlet (using /bin/sh): if [ $1 -eq 1 ] ; then # Initial installation systemctl preset mariadb.service >/dev/null 2>&1 || : fi /bin/chmod 0755 /var/lib/mysql /bin/touch /var/log/mariadb/mariadb.log /usr/sbin/update-alternatives --install /usr/bin/mysqlbug mysqlbug /usr/lib64/mysql/mysqlbug 64 preuninstall scriptlet (using /bin/sh): if [ $1 -eq 0 ] ; then # Package removal, not upgrade systemctl --no-reload disable mariadb.service > /dev/null 2>&1 || : systemctl stop mariadb.service > /dev/null 2>&1 || : fi postuninstall scriptlet (using /bin/sh): systemctl daemon-reload >/dev/null 2>&1 || : if [ $1 -ge 1 ] ; then # Package upgrade, not uninstall systemctl try-restart mariadb.service >/dev/null 2>&1 || : fi if [ $1 -eq 0 ] ; then /usr/sbin/update-alternatives --remove mysqlbug /usr/lib64/mysql/mysqlbug fi [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# id mysql uid=27(mysql) gid=27(mysql) groups=27(mysql) [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# grep mysql /etc/passwd mysql:x:27:27:MariaDB Server:/var/lib/mysql:/sbin/nologin [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]#
2>.启动Mariadb服务端
[root@node102.yinzhengjie.org.cn ~]# ll /var/lib/mysql/ #默认的数据存储目录为空 total 0 [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# systemctl start mariadb #第一次启动Mariadb数据库,观察数据存储目录("/var/lib/mysql/")是否有数据生成。 [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# ll /var/lib/mysql/ #再次查看数据存储目录,发现的确有相应的数据生成。 total 37852 -rw-rw---- 1 mysql mysql 16384 Oct 25 06:26 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 Oct 25 06:26 aria_log_control -rw-rw---- 1 mysql mysql 18874368 Oct 25 06:26 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Oct 25 06:26 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Oct 25 06:26 ib_logfile1 drwx------ 2 mysql mysql 4096 Oct 25 06:26 mysql srwxrwxrwx 1 mysql mysql 0 Oct 25 06:26 mysql.sock drwx------ 2 mysql mysql 4096 Oct 25 06:26 performance_schema drwx------ 2 mysql mysql 6 Oct 25 06:26 test [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# systemctl status mariadb #查看Mariadb服务的状态 ● mariadb.service - MariaDB database server Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled) Active: active (running) since Fri 2019-10-25 06:26:44 CST; 9min ago Process: 7830 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS) Process: 7743 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS) Main PID: 7829 (mysqld_safe) CGroup: /system.slice/mariadb.service ├─7829 /bin/sh /usr/bin/mysqld_safe --basedir=/usr └─7991 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariad... Oct 25 06:26:42 node102.yinzhengjie.org.cn mariadb-prepare-db-dir[7743]: MySQL manual for more instructions. Oct 25 06:26:42 node102.yinzhengjie.org.cn mariadb-prepare-db-dir[7743]: Please report any problems at http://mariadb.org/jira Oct 25 06:26:42 node102.yinzhengjie.org.cn mariadb-prepare-db-dir[7743]: The latest information about MariaDB is available at http://mari...org/. Oct 25 06:26:42 node102.yinzhengjie.org.cn mariadb-prepare-db-dir[7743]: You can find additional information about the MySQL part at: Oct 25 06:26:42 node102.yinzhengjie.org.cn mariadb-prepare-db-dir[7743]: http://dev.mysql.com Oct 25 06:26:42 node102.yinzhengjie.org.cn mariadb-prepare-db-dir[7743]: Consider joining MariaDB's strong and vibrant community: Oct 25 06:26:42 node102.yinzhengjie.org.cn mariadb-prepare-db-dir[7743]: https://mariadb.org/get-involved/ Oct 25 06:26:42 node102.yinzhengjie.org.cn mysqld_safe[7829]: 191025 06:26:42 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'. Oct 25 06:26:42 node102.yinzhengjie.org.cn mysqld_safe[7829]: 191025 06:26:42 mysqld_safe Starting mysqld daemon with databases from /va.../mysql Oct 25 06:26:44 node102.yinzhengjie.org.cn systemd[1]: Started MariaDB database server. Hint: Some lines were ellipsized, use -l to show in full. [root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# ps aux | grep mariadb | grep -v grep #查看Mariadb服务的mariadb进程是否存在 mysql 7991 0.0 2.0 968788 79532 ? Sl 06:26 0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/li b64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock[root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# ss -ntl #我们发现mariadb的3306端口启动成功了,mariadb不愧是MySQL的一个分支,连默认的服务端口都一致 State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 50 *:3306 *:* LISTEN 0 128 *:22 *:* LISTEN 0 128 :::22 :::* [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# yum -y install psmisc #安装pstree命令行工具 Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: mirrors.nju.edu.cn * extras: mirrors.nju.edu.cn * updates: mirrors.nju.edu.cn Resolving Dependencies --> Running transaction check ---> Package psmisc.x86_64 0:22.20-15.el7 will be updated ---> Package psmisc.x86_64 0:22.20-16.el7 will be an update --> Finished Dependency Resolution Dependencies Resolved ================================================================================================================================================= Package Arch Version Repository Size ================================================================================================================================================= Updating: psmisc x86_64 22.20-16.el7 base 141 k Transaction Summary ================================================================================================================================================= Upgrade 1 Package Total download size: 141 k Downloading packages: Delta RPMs disabled because /usr/bin/applydeltarpm not installed. psmisc-22.20-16.el7.x86_64.rpm | 141 kB 00:00:05 Running transaction check Running transaction test Transaction test succeeded Running transaction Updating : psmisc-22.20-16.el7.x86_64 1/2 Cleanup : psmisc-22.20-15.el7.x86_64 2/2 Verifying : psmisc-22.20-16.el7.x86_64 1/2 Verifying : psmisc-22.20-15.el7.x86_64 2/2 Updated: psmisc.x86_64 0:22.20-16.el7 Complete! [root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# ps aux | grep mariadb | grep -v grep mysql 7991 0.0 2.0 968788 79532 ? Sl 06:26 0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/li b64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock[root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# pstree -p systemd(1)─┬─NetworkManager(2796)─┬─dhclient(2853) │ ├─{NetworkManager}(2825) │ └─{NetworkManager}(2828) ├─agetty(3088) ├─atd(2811) ├─auditd(2756)───{auditd}(2761) ├─crond(2823) ├─dbus-daemon(2791) ├─lvmetad(1523) ├─mysqld_safe(7829)───mysqld(7991)─┬─{mysqld}(7995) │ ├─{mysqld}(7996) │ ├─{mysqld}(7997) │ ├─{mysqld}(7998) │ ├─{mysqld}(7999) │ ├─{mysqld}(8000) │ ├─{mysqld}(8001) │ ├─{mysqld}(8002) │ ├─{mysqld}(8003) │ ├─{mysqld}(8004) │ ├─{mysqld}(8005) │ ├─{mysqld}(8006) │ ├─{mysqld}(8007) │ ├─{mysqld}(8008) │ ├─{mysqld}(8009) │ ├─{mysqld}(8010) │ ├─{mysqld}(8018) │ └─{mysqld}(8019) ├─polkitd(2786)─┬─{polkitd}(2797) │ ├─{polkitd}(2799) │ ├─{polkitd}(2809) │ ├─{polkitd}(2821) │ ├─{polkitd}(2824) │ └─{polkitd}(2826) ├─rsyslogd(3078)─┬─{rsyslogd}(3085) │ └─{rsyslogd}(3086) ├─sshd(3074)─┬─sshd(3812)───bash(3814)───pstree(8129) │ └─sshd(4296)───bash(4298) ├─systemd-journal(1497) ├─systemd-logind(2788) ├─systemd-udevd(1518) └─tuned(3073)─┬─{tuned}(3353) ├─{tuned}(3354) ├─{tuned}(3355) └─{tuned}(3368) [root@node102.yinzhengjie.org.cn ~]#
3>.连接mysql服务器
[root@node102.yinzhengjie.org.cn ~]# mysql #使用mysql命令行工具连接Mariadb数据库,默认密码为空 Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 4 Server version: 5.5.64-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)]> h #查看帮助信息 General information about MariaDB can be found at http://mariadb.org List of all MySQL commands: Note that all text commands must be first on line and end with ';' ? (?) Synonym for `help'. clear (c) Clear the current input statement. connect ( ) Reconnect to the server. Optional arguments are db and host. delimiter (d) Set statement delimiter. edit (e) Edit command with $EDITOR. ego (G) Send command to mysql server, display result vertically. exit (q) Exit mysql. Same as quit. go (g) Send command to mysql server. help (h) Display this help. nopager ( ) Disable pager, print to stdout. notee ( ) Don't write into outfile. pager (P) Set PAGER [to_pager]. Print the query results via PAGER. print (p) Print current command. prompt (R) Change your mysql prompt. quit (q) Quit mysql. rehash (#) Rebuild completion hash. source (.) Execute an SQL script file. Takes a file name as an argument. status (s) Get status information from the server. system (!) Execute a system shell command. tee (T) Set outfile [to_outfile]. Append everything into given outfile. use (u) Use another database. Takes database name as argument. charset (C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets. warnings (W) Show warnings after every statement. nowarning (w) Don't show warnings after every statement. For server side help, type 'help contents' MariaDB [(none)]> MariaDB [(none)]> QUIT #断开Mariadb的连接 Bye [root@node102.yinzhengjie.org.cn ~]#
[root@node102.yinzhengjie.org.cn ~]# ll /var/lib/mysql/mysql.sock #默认的本地socket连接文件 srwxrwxrwx 1 mysql mysql 0 Oct 25 06:26 /var/lib/mysql/mysql.sock [root@node102.yinzhengjie.org.cn ~]# [root@node102.yinzhengjie.org.cn ~]# mysql -S /var/lib/mysql/mysql.sock #使用"-S"指定默认的本地socket连接文件,默认路径在"/var/lib/mysql/mysql.sock",如果没有修改默认路径咱们可以不指定。 Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 6 Server version: 5.5.64-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)]> help General information about MariaDB can be found at http://mariadb.org List of all MySQL commands: Note that all text commands must be first on line and end with ';' ? (?) Synonym for `help'. clear (c) Clear the current input statement. connect ( ) Reconnect to the server. Optional arguments are db and host. delimiter (d) Set statement delimiter. edit (e) Edit command with $EDITOR. ego (G) Send command to mysql server, display result vertically. exit (q) Exit mysql. Same as quit. go (g) Send command to mysql server. help (h) Display this help. nopager ( ) Disable pager, print to stdout. notee ( ) Don't write into outfile. pager (P) Set PAGER [to_pager]. Print the query results via PAGER. print (p) Print current command. prompt (R) Change your mysql prompt. quit (q) Quit mysql. rehash (#) Rebuild completion hash. source (.) Execute an SQL script file. Takes a file name as an argument. status (s) Get status information from the server. system (!) Execute a system shell command. tee (T) Set outfile [to_outfile]. Append everything into given outfile. use (u) Use another database. Takes database name as argument. charset (C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets. warnings (W) Show warnings after every statement. nowarning (w) Don't show warnings after every statement. For server side help, type 'help contents' MariaDB [(none)]> EXIT Bye [root@node102.yinzhengjie.org.cn ~]#
4>.MariaDB客户端(使用和MySQL服务一样的mysql客户端连接工具)命令
[root@node102.yinzhengjie.org.cn ~]# mysql --help #查看mysql命令的帮助信息 mysql Ver 15.1 Distrib 5.5.64-MariaDB, for Linux (x86_64) using readline 5.1 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Usage: mysql [OPTIONS] [database] Default options are read from the following files in the given order: /etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf The following groups are read: mysql client client-server client-mariadb The following options may be given as the first argument: --print-defaults Print the program argument list and exit. --no-defaults Don't read default options from any option file. The following specify which files/extra groups are read (specified before remaining options): --defaults-file=# Only read default options from the given file #. --defaults-extra-file=# Read this file after the global files are read. --defaults-group-suffix=# Additionally read default groups with # appended as a suffix. -?, --help Display this help and exit. -I, --help Synonym for -? --abort-source-on-error Abort 'source filename' operations in case of errors --auto-rehash Enable automatic rehashing. One doesn't need to use 'rehash' to get table and field completion, but startup and reconnecting may take a longer time. Disable with --disable-auto-rehash. (Defaults to on; use --skip-auto-rehash to disable.) -A, --no-auto-rehash No automatic rehashing. One has to use 'rehash' to get table and field completion. This gives a quicker start of mysql and disables rehashing on reconnect. --auto-vertical-output Automatically switch to vertical output mode if the result is wider than the terminal width. -B, --batch Don't use history file. Disable interactive behavior. (Enables --silent.) --binary-as-hex Print binary data as hex --character-sets-dir=name Directory for character set files. --column-type-info Display column type information. -c, --comments Preserve comments. Send comments to the server. The default is --skip-comments (discard comments), enable with --comments. -C, --compress Use compression in server/client protocol. -#, --debug[=#] This is a non-debug version. Catch this and exit. --debug-check Check memory and open file usage at exit. -T, --debug-info Print some debug info at exit. -D, --database=name Database to use. --default-character-set=name Set the default character set. --delimiter=name Delimiter to be used. -e, --execute=name Execute command and quit. (Disables --force and history file.) -E, --vertical Print the output of a query (rows) vertically. -f, --force Continue even if we get an SQL error. Sets abort-source-on-error to 0 -G, --named-commands Enable named commands. Named commands mean this program's internal commands; see mysql> help . When enabled, the named commands can be used from any line of the query, otherwise only from the first line, before an enter. Disable with --disable-named-commands. This option is disabled by default. -i, --ignore-spaces Ignore space after function names. --init-command=name SQL Command to execute when connecting to MySQL server. Will automatically be re-executed when reconnecting. --local-infile Enable/disable LOAD DATA LOCAL INFILE. -b, --no-beep Turn off beep on error. -h, --host=name Connect to host. -H, --html Produce HTML output. -X, --xml Produce XML output. --line-numbers Write line numbers for errors. (Defaults to on; use --skip-line-numbers to disable.) -L, --skip-line-numbers Don't write line number for errors. -n, --unbuffered Flush buffer after each query. --column-names Write column names in results. (Defaults to on; use --skip-column-names to disable.) -N, --skip-column-names Don't write column names in results. --sigint-ignore Ignore SIGINT (CTRL-C). -o, --one-database Ignore statements except those that occur while the default database is the one named at the command line. --pager[=name] Pager to use to display results. If you don't supply an option, the default pager is taken from your ENV variable PAGER. Valid pagers are less, more, cat [> filename], etc. See interactive help (h) also. This option does not work in batch mode. Disable with --disable-pager. This option is disabled by default. -p, --password[=name] Password to use when connecting to server. If password is not given it's asked from the tty. -P, --port=# Port number to use for connection or 0 for default to, in order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default (3306). --progress-reports Get progress reports for long running commands (like ALTER TABLE) (Defaults to on; use --skip-progress-reports to disable.) --prompt=name Set the mysql prompt to this value. --protocol=name The protocol to use for connection (tcp, socket, pipe, memory). -q, --quick Don't cache result, print it row by row. This may slow down the server if the output is suspended. Doesn't use history file. -r, --raw Write fields without conversion. Used with --batch. --reconnect Reconnect if the connection is lost. Disable with --disable-reconnect. This option is enabled by default. (Defaults to on; use --skip-reconnect to disable.) -s, --silent Be more silent. Print results with a tab as separator, each row on new line. -S, --socket=name The socket file to use for connection. --ssl Enable SSL for connection (automatically enabled with other flags). --ssl-ca=name CA file in PEM format (check OpenSSL docs, implies --ssl). --ssl-capath=name CA directory (check OpenSSL docs, implies --ssl). --ssl-cert=name X509 cert in PEM format (implies --ssl). --ssl-cipher=name SSL cipher to use (implies --ssl). --ssl-key=name X509 key in PEM format (implies --ssl). --ssl-verify-server-cert Verify server's "Common Name" in its cert against hostname used when connecting. This option is disabled by default. -t, --table Output in table format. --tee=name Append everything into outfile. See interactive help (h) also. Does not work in batch mode. Disable with --disable-tee. This option is disabled by default. -u, --user=name User for login if not current user. -U, --safe-updates Only allow UPDATE and DELETE that uses keys. -U, --i-am-a-dummy Synonym for option --safe-updates, -U. -v, --verbose Write more. (-v -v -v gives the table output format). -V, --version Output version information and exit. -w, --wait Wait and retry if connection is down. --connect-timeout=# Number of seconds before connection timeout. --max-allowed-packet=# The maximum packet length to send to or receive from server. --net-buffer-length=# The buffer size for TCP/IP and socket communication. --select-limit=# Automatic limit for SELECT when using --safe-updates. --max-join-size=# Automatic limit for rows in a join when using --safe-updates. --secure-auth Refuse client connecting to server if it uses old (pre-4.1.1) protocol. --server-arg=name Send embedded server this as a parameter. --show-warnings Show warnings after every statement. --plugin-dir=name Directory for client-side plugins. --default-auth=name Default authentication client-side plugin to use. --binary-mode By default, ASCII '