CentOS 7.2安装MariaDB 目前的CentOS7默认可以安装MarialDB，本文主要针对默认版本。1、如果要安装最新的版本，需要自己设置安装源rpm -qa |grep mariadb yum remove mysql mysql-server mysql-libs compat-mysql51 rpm -e --nodeps mariadb-* vim /etc/yum.repos.d/MariaDB.repo # MariaDB 10.1 CentOS repository list - created 2017-02-15 20:31 UTC # http://downloads.mariadb.org/mariadb/repositories/ [mariadb] name = MariaDB baseurl = http://yum.mariadb.org/10.1/centos7-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1 2、开始安装yum -y install mariadb-server # 或 yum -y install MariaDB-server MariaDB-client安装执行yum -y install mariadb-server Loaded plugins: fastestmirror, langpacks epel                                                                                | 4.7 kB  00:00:00      extras                                                                              | 3.4 kB  00:00:00      os                                                                                  | 3.6 kB  00:00:00      updates                                                                             | 3.4 kB  00:00:00      (1/7): epel/7/x86_64/group_gz                                                       |  88 kB  00:00:00      (2/7): epel/7/x86_64/updateinfo                                                     | 951 kB  00:00:00      (3/7): extras/7/x86_64/primary_db                                                   | 156 kB  00:00:00      (4/7): os/7/x86_64/group_gz                                                         | 166 kB  00:00:00      (5/7): updates/7/x86_64/primary_db                                                  | 1.3 MB  00:00:00      (6/7): os/7/x86_64/primary_db                                                       | 6.0 MB  00:00:00      (7/7): epel/7/x86_64/primary_db                                                     | 6.6 MB  00:00:01      Determining fastest mirrors Resolving Dependencies --> Running transaction check ---> Package mariadb-server.x86_64 1:5.5.60-1.el7_5 will be installed --> Processing Dependency: mariadb-libs(x86-64) = 1:5.5.60-1.el7_5 for package: 1:mariadb-server-5.5.60-1.el7_5.x86_64 --> Processing Dependency: mariadb(x86-64) = 1:5.5.60-1.el7_5 for package: 1:mariadb-server-5.5.60-1.el7_5.x86_64 --> Processing Dependency: perl-DBI for package: 1:mariadb-server-5.5.60-1.el7_5.x86_64 --> Processing Dependency: perl-DBD-MySQL for package: 1:mariadb-server-5.5.60-1.el7_5.x86_64 --> Processing Dependency: perl(Data::Dumper) for package: 1:mariadb-server-5.5.60-1.el7_5.x86_64 --> Processing Dependency: perl(DBI) for package: 1:mariadb-server-5.5.60-1.el7_5.x86_64 --> Running transaction check ---> Package mariadb.x86_64 1:5.5.60-1.el7_5 will be installed ---> Package mariadb-libs.x86_64 1:5.5.52-1.el7 will be updated ---> Package mariadb-libs.x86_64 1:5.5.60-1.el7_5 will be an update ---> 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 ---> Package perl-Data-Dumper.x86_64 0:2.145-3.el7 will be installed --> 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 --> Processing Dependency: perl(Compress::Zlib) for package: perl-PlRPC-0.2020-14.el7.noarch --> Running transaction check ---> Package perl-IO-Compress.noarch 0:2.061-2.el7 will be installed --> Processing Dependency: perl(Compress::Raw::Zlib) >= 2.061 for package: perl-IO-Compress-2.061-2.el7.noarch --> Processing Dependency: perl(Compress::Raw::Bzip2) >= 2.061 for package: perl-IO-Compress-2.061-2.el7.noarch ---> Package perl-Net-Daemon.noarch 0:0.48-5.el7 will be installed --> Running transaction check ---> Package perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7 will be installed ---> Package perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved =========================================================================================================== Package                             Arch               Version                       Repository      Size =========================================================================================================== Installing: mariadb-server                      x86_64             1:5.5.60-1.el7_5              os              11 M Installing for dependencies: mariadb                             x86_64             1:5.5.60-1.el7_5              os             8.9 M perl-Compress-Raw-Bzip2             x86_64             2.061-3.el7                   os              32 k perl-Compress-Raw-Zlib              x86_64             1:2.061-4.el7                 os              57 k perl-DBD-MySQL                      x86_64             4.023-6.el7                   os             140 k perl-DBI                            x86_64             1.627-4.el7                   os             802 k perl-Data-Dumper                    x86_64             2.145-3.el7                   os              47 k perl-IO-Compress                    noarch             2.061-2.el7                   os             260 k perl-Net-Daemon                     noarch             0.48-5.el7                    os              51 k perl-PlRPC                          noarch             0.2020-14.el7                 os              36 k Updating for dependencies: mariadb-libs                        x86_64             1:5.5.60-1.el7_5              os             758 k Transaction Summary =========================================================================================================== Install  1 Package  (+9 Dependent packages) Upgrade             ( 1 Dependent package) Total download size: 22 M Downloading packages: Delta RPMs disabled because /usr/bin/applydeltarpm not installed. (1/11): mariadb-libs-5.5.60-1.el7_5.x86_64.rpm                                      | 758 kB  00:00:00      (2/11): mariadb-5.5.60-1.el7_5.x86_64.rpm                                                                                                            | 8.9 MB  00:00:00      (3/11): perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64.rpm                                                                                               |  32 kB  00:00:00      (4/11): perl-Compress-Raw-Zlib-2.061-4.el7.x86_64.rpm                                                                                                |  57 kB  00:00:00      (5/11): perl-DBD-MySQL-4.023-6.el7.x86_64.rpm                                                                                                        | 140 kB  00:00:00      (6/11): perl-DBI-1.627-4.el7.x86_64.rpm                                                                                                              | 802 kB  00:00:00      (7/11): perl-Data-Dumper-2.145-3.el7.x86_64.rpm                                                                                                      |  47 kB  00:00:00      (8/11): perl-IO-Compress-2.061-2.el7.noarch.rpm                                                                                                      | 260 kB  00:00:00      (9/11): perl-Net-Daemon-0.48-5.el7.noarch.rpm                                                                                                        |  51 kB  00:00:00      (10/11): perl-PlRPC-0.2020-14.el7.noarch.rpm                                                                                                         |  36 kB  00:00:00      (11/11): mariadb-server-5.5.60-1.el7_5.x86_64.rpm                                                                                                    |  11 MB  00:00:01      ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Total                                                                                                                                        15 MB/s |  22 MB  00:00:01      Running transaction check Running transaction test Transaction test succeeded Running transaction   Updating   : 1:mariadb-libs-5.5.60-1.el7_5.x86_64                                                                                                                    1/12   Installing : perl-Data-Dumper-2.145-3.el7.x86_64                                                                                                                     2/12   Installing : 1:mariadb-5.5.60-1.el7_5.x86_64                                                                                                                         3/12   Installing : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64                                                                                                             4/12   Installing : perl-Net-Daemon-0.48-5.el7.noarch                                                                                                                       5/12   Installing : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64                                                                                                              6/12   Installing : perl-IO-Compress-2.061-2.el7.noarch                                                                                                                     7/12   Installing : perl-PlRPC-0.2020-14.el7.noarch                                                                                                                         8/12   Installing : perl-DBI-1.627-4.el7.x86_64                                                                                                                             9/12   Installing : perl-DBD-MySQL-4.023-6.el7.x86_64                                                                                                                      10/12   Installing : 1:mariadb-server-5.5.60-1.el7_5.x86_64                                                                                                                 11/12   Cleanup    : 1:mariadb-libs-5.5.52-1.el7.x86_64                                                                                                                     12/12   Verifying  : 1:mariadb-server-5.5.60-1.el7_5.x86_64                                                                                                                  1/12   Verifying  : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64                                                                                                              2/12   Verifying  : perl-Net-Daemon-0.48-5.el7.noarch                                                                                                                       3/12   Verifying  : perl-Data-Dumper-2.145-3.el7.x86_64                                                                                                                     4/12   Verifying  : perl-DBD-MySQL-4.023-6.el7.x86_64                                                                                                                       5/12   Verifying  : 1:mariadb-libs-5.5.60-1.el7_5.x86_64                                                                                                                    6/12   Verifying  : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64                                                                                                             7/12   Verifying  : 1:mariadb-5.5.60-1.el7_5.x86_64                                                                                                                         8/12   Verifying  : perl-DBI-1.627-4.el7.x86_64                                                                                                                             9/12   Verifying  : perl-IO-Compress-2.061-2.el7.noarch                                                                                                                    10/12   Verifying  : perl-PlRPC-0.2020-14.el7.noarch                                                                                                                        11/12   Verifying  : 1:mariadb-libs-5.5.52-1.el7.x86_64                                                                                                                     12/12 Installed:   mariadb-server.x86_64 1:5.5.60-1.el7_5                                                                                                                                     Dependency Installed:   mariadb.x86_64 1:5.5.60-1.el7_5      perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7    perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7    perl-DBD-MySQL.x86_64 0:4.023-6.el7      perl-DBI.x86_64 0:1.627-4.el7        perl-Data-Dumper.x86_64 0:2.145-3.el7           perl-IO-Compress.noarch 0:2.061-2.el7          perl-Net-Daemon.noarch 0:0.48-5.el7      perl-PlRPC.noarch 0:0.2020-14.el7    Dependency Updated:   mariadb-libs.x86_64 1:5.5.60-1.el7_5                                                                                                                                       Complete! 安装完成MariaDB，首先启动MariaDBsystemctl start mariadb设置为开启启动systemctl enable mariadb Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.3、初始化数据。接下来进行MariaDB的相关简单配置：mysql_secure_installationmysql_secure_installation 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): OK, successfully used password, moving on... Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorisation. #设置密码，是否设置root用户密码，输入Y并回车或直接回车 Set root password? [Y/n] y #设置root用户的密码 New password: #再输入一次你设置的密码 Re-enter new password: #root密码设置成功 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. #是否删除匿名用户，输入Y并回车 Remove anonymous users? [Y/n] 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. #是否禁止root远程登录，输入n并回车 Disallow root login remotely? [Y/n] n ... skipping. 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. #是否删除test数据库，输入n并回车 Remove test database and access to it? [Y/n] n ... skipping. Reloading the privilege tables will ensure that all changes made so far will take effect immediately. #是否重新加载权限表，回车 Reload privilege tables now? [Y/n] ... 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! 操作执行到这里，数据库的初始化已基本完成。现在测试登录 mysql -h127.0.01 -uroot -p Enter password: Welcome to the MariaDB monitor.  Commands end with ; or \g. Your MariaDB connection id is 8 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)]> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | | test               | +--------------------+ 4 rows in set (0.00 sec) 4、配置数据库字符集1）编辑文件/etc/my.cnfvim /etc/my.cnf #在[mysqld]标签下添加 init_connect='SET collation_connection = utf8_unicode_ci' init_connect='SET NAMES utf8' character-set-server=utf8 collation-server=utf8_unicode_ci skip-character-set-client-handshake #端口 port=33172）编辑文件/etc/my.cnf.d/client.cnfvim /etc/my.cnf.d/client.cnf #在[client]中添加 default-character-set=utf83）编辑文件/etc/my.cnf.d/mysql-clients.cnfvim /etc/my.cnf.d/mysql-clients.cnf #在[mysql]中添加 default-character-set=utf8全部配置完成，重启mariadbsystemctl restart mariadb 登陆数据库，查看配置后的字符集信息MariaDB [(none)]> show variables like "%character%"; +--------------------------+----------------------------+ | Variable_name            | Value                      | +--------------------------+----------------------------+ | character_set_client     | utf8                       | | character_set_connection | utf8                       | | character_set_database   | utf8                       | | character_set_filesystem | binary                     | | character_set_results    | utf8                       | | character_set_server     | utf8                       | | character_set_system     | utf8                       | | character_sets_dir       | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec) MariaDB [(none)]> show variables like "%collation%"; +----------------------+-----------------+ | Variable_name        | Value           | +----------------------+-----------------+ | collation_connection | utf8_unicode_ci | | collation_database   | utf8_unicode_ci | | collation_server     | utf8_unicode_ci | +----------------------+-----------------+ 3 rows in set (0.00 sec) MariaDB [(none)]> select version(); +----------------+ | version()      | +----------------+ | 5.5.60-MariaDB | +----------------+ 1 row in set (0.00 sec)  5、添加用户，设置权限创建用户：#创建用户命令:用户名 kftest,密码1234567 create user kftest@localhost identified by '1234567'; #直接创建用户并授权的命令 grant all on *.* to kftest@localhost indentified by '1234567';授权用户：#授予外网登陆权限 grant all privileges on *.* to kftest@'%' identified by '1234567'; #授予权限并且可以授权 grant all privileges on *.* to kftest@'hostname' identified by '1234567' with grant option;简单的用户和权限配置基本就这样了。其中只授予部分权限把 其中 all privileges或者all改为select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file其中一部分。6、其他查看MariaDB启动状态：systemctl status mariadbsystemd重载此配置文件，刚刚配置的服务需要让systemctl能识别，就必须刷新配置：systemctl daemon-reload如果没有权限可以使用sudosudo systemctl daemon-reload停止MariaDB服务：systemctl stop mariadb禁止开机启动：systemctl disable mariadb设置防火墙远程端口firewall-cmd --zone=public --add-port=3308/tcp --permanent刷新重启防火墙firewall-cmd --reload 

使用cmd命令状态下对MySQL数据导入导出 MySQL导入导出.sql文件步骤如下：一.MySQL的命令行模式的设置：桌面->我的电脑->属性->环境变量->新建->PATH=“；path\mysql\bin;”其中path为MySQL的安装路径。或者使用进入mysql的bin目录二.简单的介绍一下命令行进入MySQL的方法：1.C:\>mysql -h hostname -u username -p按ENTER键，等待然后输入密码。这里hostname为服务器的名称，如localhost，username为MYSQL的用户名，如root。进入命令行后可以直接操作MySQL了。2.简单介绍一下MySQL命令：mysql->CREATE DATABASE dbname;//创建数据库mysql->CREATE TABLE tablename;//创建表mysql->SHOW DATABASES;//显示数据库信息，有那些可用的数据库。mysql->USE dbname;//选择数据库mysql->SHOW TABLES;//显示表信息，有那些可用的表mysql->DESCRIBE tablename;//显示创建的表的信息三.从数据库导出数据库文件：1.将数据库mydb导出到e:\mysql\mydb.sql文件中：打开开始->运行->输入cmd 进入命令行模式c:\>mysqldump -h localhost -u root -p mydb >e:\mysql\mydb.sql然后输入密码，等待一会导出就成功了，可以到目标文件中检查是否成功。2.将数据库mydb中的mytable导出到e:\mysql\mytable.sql文件中：c:\>mysqldump -h localhost -u root -p mydb mytable>e:\mysql\mytable.sql3.将数据库mydb的结构导出到e:\mysql\mydb_stru.sql文件中：c:\>mysqldump -h localhost -u root -p mydb --add-drop-table >e:\mysql\mydb_stru.sql//-h localhost可以省略，其一般在虚拟主机上用四.从外部文件导入数据到数据库中：从e:\mysql\mydb2.sql中将文件中的SQL语句导入数据库中：1.从命令行进入mysql，然后用命令CREATE DATABASE mydb2;创建数据库mydb2。2.退出mysql 可以输入命令exit；或者quit；3.在CMD中输入下列命令：c:\>mysql -h localhost -u root -p mydb2 < e:\mysql\mydb2.sql然后输入密码，就OK了。五.下面谈一下关于导入文件大小限制问题的解决：默认情况下：MySQL 对导入文件大小有限制的，最大为2M，所以当文件很大时候，直接无法导入，下面就这个问题的解决列举如下：1.在php.ini中修改相关参数：影响mysql导入文件大小的参数有三个：memory_limit=128M,upload_max_filesize=2M,post_max_size=8M修改upload_max_filesize=200 M 这里修改满足你需要的大小，可以同时修改其他两项memory_limit=250M post_max_size=200M这样就可以导入200M以下的.sql文件了。上文中是把MySQL放置在系统路径中，其实不妨也行。例如我的MySQL安装目录为D:\MySQL Server 5.0；则首先用cmd打开dos窗口，然后输入D:(没有'\')回车此时应该会出现D:\>这样的标记，然后在其后面输入D:\MySQL Server 5.0\bin回车（顺序颠倒也行的）出现D:\MySQL Server 5.0\bin> 接着输入mysqldump -u用户名 -p 数据库名 > 数据库名.sql（也可以输入路径）；具体用法参照上文。导入文件同样，只是改了‘>’为‘<’就行了。或者直接用source 也行：常用source 命令进入mysql数据库控制台如mysql -u root -p（没必要,省略）mysql>use 数据库然后使用source命令，后面参数为脚本文件(如这里用到的.sql)mysql>source d:wcnc_db.sql 

MySQL数据删除语句 DELETE语句：delete from 表名 [where <删除条件>] ;如果删除的行的主键值被其他表引用，那么删除被引用的行时，将报告与约束冲突的错误信息。DELETE语句只要删除就是删除整行记录，不会只删除单个列，所以在DELETE后不能出现列名称。 TRUNCATE TABLE语句：语法：TRUNCATE TABLE 表名TRUNCATE TABLE 用来删除表中的所有行，功能上它类似于没有WHERE子句的DELETE的语句。但是TRUNCATE TABLE比DELETE执行速度快，使用的系统资源和事务日志资源更少，并且删除数据后表的标识列会重新开始编号。 提示：TRUNCATE TABLE 用来删除表中的所有行，表的结构、列、约束、索引等不会被改动。TRUNCATE TABLE 不能用于有外键约束引用的表，这种情况下，就需要使用DELETE语句。在实际工作中，不建议使用TRUNCATE TABLE语句，因为使用它删除的数据不能恢复还原。

登录与退出MySQL数据库 当MySQL服务启动后，我们就可以使用命令模式对MySQL数据库进行操作与管理。进行操作和管理的第一步当然就是登录MySQL数据库。连接MySQL的格式：Mysql -h主机地址 -u用户名 -p用户密码那么到底怎么打开数据库呢？我就给大家做个例子。我做的这个例子是在win7系统下的本地测试的。所以里面的具体操作应该根据大伙的实际环境而定。找到你们的MySQL安装路径，在MySQL安装目录下面找到bin目录，然后复制整个路径。然后右击  计算机→属性→系统保护→高级→环境变量，在弹出的环境变量窗口中找到系统变量中的Path变量，编辑变量值，在变量值的尾部加上 “;D:\mysql\bin”这样的代码，由于我的MySQL是安装在D盘的根目录下的，所以这个路径要根据你当时的安装路径来填写。完成上面的工作，只是MySQL的配置工作做完。正式的操作现在才要开始！在这个首先，在运行中输入“CMD”打开DOS窗口，在键入命令“mysql -hlocalhost -uroot -p”，回车后提示您输入密码。如果你刚安装好的用户，则是超级用户root是没有密码的，故直接回车即可进入MySQL中。代码截图如下：现在我来解释下我刚才键入的命令是什么意思？mysql -hlocalhost -uroot -pMysql ：这个是mysql 连接的固定模式，具体不用多解释Hlocalhost：h后面加了个localhost，前面我也说了，我这个例子是在本地环境下做的，所以我填写了本机的本地地址也就是localhost  或者也可以是  127.0.0.1 Uroot：root为我的用户名。由于我是刚安装的用户，所以则是root超级用户,而且是没有密码的P：正如上面所说，我的是刚安装的mysql的用户，是超级用户，没有密码，所以我的p后面则没写，也就是为空。好了，现在大家对我刚才写的mysql数据库连接命令明白了嘛？？不明白的可以直接在下面留言给我哦。我会再次帮你解答的！假设远程主机的IP地址为192.168.9.168、用户名为root、密码为rootpwd，则键入以下命令“mysql -h 192.168.9.168 -u root -p”，回车后提示你输入密码——rootpwd。好了，到此为止，你的数据库已经连接成功了！那么数据库连接成功了，退出呢？退出的命令：Quit在DOS窗口里面键入“quit”回车就是可以退出数据库啦！这就是数据库的登录和退出！你会了吗？如果有什么问题，你可以直接留言给我，希望我可以帮到你哦！

MySQL数据库基础操作命令(1) MySQL数据库连接命令：mysql -hlocalhost(localhost为您的数据库地址) -uroot(root为数据库登陆用户名) -p(由于我当时测试是在本地进行，所以数据库的密码为空，所以没有填写。当然，如果你有密码的话，得这个位置填写)打开数据库后，查看数据库的命令：show databases;如果没有库(当然，在这里是指没有你需要的数据库时)，我们可以新建个库，新建库的命令：create database 库名;想调用并使用某个数据库的命令：use 数据库名称；调用并成功使用开启数据库后，查看当前操作的数据库中的表的命令：show tables;如果没有表，可以新建一张表，新建表的命令：create table 表名称(字段1名称 字段1类型,字段2名称 字段2类型,字段3名称 字段3类型……,字段x名称 字段x类型);先暂时发这些基础的命令，其他的我会后续更新！由于时间仓促，没有办法及时的校队，所以可能在局部有错误，如有错误，希望高手指出！小弟在这里先谢谢了!