Mariadb数据库配置及管理
来源：互联网 发布：360数据恢复手机版 编辑：程序博客网 时间：2024/06/06 16:26
以下内容都在redhat7.0上练习
1 安装mariadb
[root@test ~]# yum install mariadb-server -y ###安装mariadb[root@test ~]# systemctl start mariadb ##开启mariadb服务 [root@test ~]# mysql ##登陆MariaDB [(none)]> quit ##退出mariadb具体代码块如下：[root@test ~]# yum install mariadb-server -yLoaded plugins: langpacksrhel_dvd | 4.1 kB 00:00 (1/2): rhel_dvd/group_gz | 134 kB 00:00 (2/2): rhel_dvd/primary_db | 3.4 MB 00:00 Resolving Dependencies--> Running transaction check---> Package mariadb-server.x86_64 1:5.5.35-3.el7 will be installed--> Processing Dependency: mariadb(x86-64) = 1:5.5.35-3.el7 for package: 1:mariadb-server-5.5.35-3.el7.x86_64--> Processing Dependency: perl(DBI) for package: 1:mariadb-server-5.5.35-3.el7.x86_64--> Processing Dependency: perl(Data::Dumper) for package: 1:mariadb-server-5.5.35-3.el7.x86_64--> Processing Dependency: perl-DBD-MySQL for package: 1:mariadb-server-5.5.35-3.el7.x86_64--> Processing Dependency: perl-DBI for package: 1:mariadb-server-5.5.35-3.el7.x86_64--> Running transaction check---> Package mariadb.x86_64 1:5.5.35-3.el7 will be installed---> Package perl-DBD-MySQL.x86_64 0:4.023-5.el7 will be installed---> Package perl-DBI.x86_64 0:1.627-4.el7 will be installed--> Processing Dependency: perl(RPC::PlClient) >= 0.2000 for package: perl-DBI-1.627-4.el7.x86_64--> Processing Dependency: perl(RPC::PlServer) >= 0.2001 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(Compress::Zlib) 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(Net::Daemon::Test) 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::Bzip2) >= 2.061 for package: perl-IO-Compress-2.061-2.el7.noarch--> Processing Dependency: perl(Compress::Raw::Zlib) >= 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 ResolutionDependencies Resolved================================================================================ Package Arch Version Repository Size================================================================================Installing: mariadb-server x86_64 1:5.5.35-3.el7 rhel_dvd 11 MInstalling for dependencies: mariadb x86_64 1:5.5.35-3.el7 rhel_dvd 8.9 M perl-Compress-Raw-Bzip2 x86_64 2.061-3.el7 rhel_dvd 32 k perl-Compress-Raw-Zlib x86_64 1:2.061-4.el7 rhel_dvd 57 k perl-DBD-MySQL x86_64 4.023-5.el7 rhel_dvd 140 k perl-DBI x86_64 1.627-4.el7 rhel_dvd 802 k perl-Data-Dumper x86_64 2.145-3.el7 rhel_dvd 47 k perl-IO-Compress noarch 2.061-2.el7 rhel_dvd 260 k perl-Net-Daemon noarch 0.48-5.el7 rhel_dvd 51 k perl-PlRPC noarch 0.2020-14.el7 rhel_dvd 36 kTransaction Summary================================================================================Install 1 Package (+9 Dependent packages)Total download size: 21 MInstalled size: 107 MDownloading packages:(1/10): mariadb-5.5.35-3.el7.x86_64.rpm | 8.9 MB 00:00 (2/10): mariadb-server-5.5.35-3.el7.x86_64.rpm | 11 MB 00:00 (3/10): perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64.rpm | 32 kB 00:00 (4/10): perl-Compress-Raw-Zlib-2.061-4.el7.x86_64.rpm | 57 kB 00:00 (5/10): perl-DBD-MySQL-4.023-5.el7.x86_64.rpm | 140 kB 00:00 (6/10): perl-Data-Dumper-2.145-3.el7.x86_64.rpm | 47 kB 00:00 (7/10): perl-DBI-1.627-4.el7.x86_64.rpm | 802 kB 00:00 (8/10): perl-IO-Compress-2.061-2.el7.noarch.rpm | 260 kB 00:00 (9/10): perl-Net-Daemon-0.48-5.el7.noarch.rpm | 51 kB 00:00 (10/10): perl-PlRPC-0.2020-14.el7.noarch.rpm | 36 kB 00:00 --------------------------------------------------------------------------------Total 29 MB/s | 21 MB 00:00 Running transaction checkRunning transaction testTransaction test succeededRunning transaction Installing : perl-Data-Dumper-2.145-3.el7.x86_64 1/10 Installing : 1:mariadb-5.5.35-3.el7.x86_64 2/10 Installing : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64 3/10 Installing : perl-Net-Daemon-0.48-5.el7.noarch 4/10 Installing : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64 5/10 Installing : perl-IO-Compress-2.061-2.el7.noarch 6/10 Installing : perl-PlRPC-0.2020-14.el7.noarch 7/10 Installing : perl-DBI-1.627-4.el7.x86_64 8/10 Installing : perl-DBD-MySQL-4.023-5.el7.x86_64 9/10 Installing : 1:mariadb-server-5.5.35-3.el7.x86_64 10/10 Verifying : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64 1/10 Verifying : perl-Net-Daemon-0.48-5.el7.noarch 2/10 Verifying : perl-Data-Dumper-2.145-3.el7.x86_64 3/10 Verifying : perl-PlRPC-0.2020-14.el7.noarch 4/10 Verifying : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64 5/10 Verifying : 1:mariadb-server-5.5.35-3.el7.x86_64 6/10 Verifying : perl-DBI-1.627-4.el7.x86_64 7/10 Verifying : 1:mariadb-5.5.35-3.el7.x86_64 8/10 Verifying : perl-DBD-MySQL-4.023-5.el7.x86_64 9/10 Verifying : perl-IO-Compress-2.061-2.el7.noarch 10/10 Installed: mariadb-server.x86_64 1:5.5.35-3.el7 Dependency Installed: mariadb.x86_64 1:5.5.35-3.el7 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-5.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 Complete![root@test ~]# systemctl start mariadb[root@test ~]# mysqlWelcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 2Server version: 5.5.35-MariaDB MariaDB ServerCopyright (c) 2000, 2013, Oracle, Monty Program Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> quitBye2.初始化mysql数据库
[root@test ~]# netstat -antlpe | grep mysql ##查看mysql开启端口[root@test ~]# vim /etc/my.cnf ##设置mysql配置文件skip-networking=1 ##添加这行 1表示不允许网络访问[root@test ~]# systemctl restart mariadb ##重启mariadb[root@test ~]# netstat -antlpe | grep mysql ##再次查看mysql开启端口[root@test ~]# mysql_secure_installation ##初始化mysql数据库Enter current password for root (enter for none): ##直接按回车Set root password? [Y/n] y ##是否设置root密码Remove anonymous users? [Y/n] y ##是否移除匿名用户登陆Disallow root login remotely? [Y/n] y ##是否禁止远程root用户登陆，y表示禁止Remove test database and access to it? [Y/n] y ##是否删除测试数据库并访问它Reload privilege tables now? [Y/n] y ##是否重新加载特权表具体代码块如下：[root@test ~]# netstat -antlpe | grep mysql ##查看mysql开启端口tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 27 39949 2512/mysqld [root@test ~]# vim /etc/my.cnf ##设置mysql配置文件skip-networking=1 ##添加这行 1表示不允许网络访问[root@test ~]# systemctl restart mariadb ##重启mariadb[root@test ~]# netstat -antlpe | grep mysql ##再次查看mysql开启端口[root@test ~]# mysql_secure_installation ##初始化mysql数据库/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not foundNOTE: 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 currentpassword for the root user. If you've just installed MariaDB, andyou 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 MariaDBroot user without the proper authorisation.Set root password? [Y/n] 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 anyoneto log into MariaDB without having to have a user account created forthem. This is intended only for testing, and to make the installationgo a bit smoother. You should remove them before moving into aproduction environment.Remove anonymous users? [Y/n] y ##是否移除匿名用户登陆 ... Success!Normally, root should only be allowed to connect from 'localhost'. Thisensures that someone cannot guess at the root password from the network.Disallow root login remotely? [Y/n] y ##是否禁止远程root用户登陆，y表示禁止 ... Success!By default, MariaDB comes with a database named 'test' that anyone canaccess. This is also intended only for testing, and should be removedbefore moving into a production environment.Remove test database and access to it? [Y/n] y ##是否删除测试数据库并访问它 - Dropping test database... ... Success! - Removing privileges on test database... ... Success!Reloading the privilege tables will ensure that all changes made so farwill take effect immediately.Reload privilege tables now? [Y/n] y ##是否重新加载特权表 ... Success!Cleaning up...All done! If you've completed all of the above steps, your MariaDBinstallation should now be secure.Thanks for using MariaDB!3.数据库的基本sql语句操作
1.登陆及查询
[root@test ~]# mysql -uroot -p Enter password: ##输入密码无回显MariaDB [(none)]> quit[root@test ~]# mysql -uroot -predhat ##直接输入用户和密码，一般不建议这种MariaDB [(none)]> quitMariaDB [(none)]> show databases; ##显示数据库MariaDB [(none)]> use mysql; ##进入mysql数据库MariaDB [mysql]> show tables; ##显示当前库中表的名称MariaDB [mysql]> desc user; ##查询user表的结构（显示所有字段的名称）MariaDB [mysql]> select * from user; ##查询user表中的所有内容（*可以用此表中的任何子段来代替）MariaDB [mysql]> select Password from user; ##查询user表中的password内容具体代码块如下：[root@test ~]# mysql -uroot -pEnter password: Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 12Server version: 5.5.35-MariaDB MariaDB ServerCopyright (c) 2000, 2013, Oracle, Monty Program 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 |+--------------------+3 rows in set (0.00 sec)MariaDB [(none)]> use mysql; ##进入mysql数据库Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedMariaDB [mysql]> show tables; ##显示mysql数据库中表的名称+---------------------------+| Tables_in_mysql |+---------------------------+| columns_priv || db || event || func || general_log || help_category || help_keyword || help_relation || help_topic || host || ndb_binlog_index || plugin || proc || procs_priv || proxies_priv || servers || slow_log || tables_priv || time_zone || time_zone_leap_second || time_zone_name || time_zone_transition || time_zone_transition_type || user |+---------------------------+24 rows in set (0.00 sec)MariaDB [mysql]> desc user; ##查询user表的结构（显示所有字段的名称）+------------------------+-----------------------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+------------------------+-----------------------------------+------+-----+---------+-------+| Host | char(60) | NO | PRI | | || User | char(16) | NO | PRI | | || Password | char(41) | NO | | | || Select_priv | enum('N','Y') | NO | | N | || Insert_priv | enum('N','Y') | NO | | N | || Update_priv | enum('N','Y') | NO | | N | || Delete_priv | enum('N','Y') | NO | | N | || Create_priv | enum('N','Y') | NO | | N | || Drop_priv | enum('N','Y') | NO | | N | || Reload_priv | enum('N','Y') | NO | | N | || Shutdown_priv | enum('N','Y') | NO | | N | || Process_priv | enum('N','Y') | NO | | N | || File_priv | enum('N','Y') | NO | | N | || Grant_priv | enum('N','Y') | NO | | N | || References_priv | enum('N','Y') | NO | | N | || Index_priv | enum('N','Y') | NO | | N | || Alter_priv | enum('N','Y') | NO | | N | || Show_db_priv | enum('N','Y') | NO | | N | || Super_priv | enum('N','Y') | NO | | N | || Create_tmp_table_priv | enum('N','Y') | NO | | N | || Lock_tables_priv | enum('N','Y') | NO | | N | || Execute_priv | enum('N','Y') | NO | | N | || Repl_slave_priv | enum('N','Y') | NO | | N | || Repl_client_priv | enum('N','Y') | NO | | N | || Create_view_priv | enum('N','Y') | NO | | N | || Show_view_priv | enum('N','Y') | NO | | N | || Create_routine_priv | enum('N','Y') | NO | | N | || Alter_routine_priv | enum('N','Y') | NO | | N | || Create_user_priv | enum('N','Y') | NO | | N | || Event_priv | enum('N','Y') | NO | | N | || Trigger_priv | enum('N','Y') | NO | | N | || Create_tablespace_priv | enum('N','Y') | NO | | N | || ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | || ssl_cipher | blob | NO | | NULL | || x509_issuer | blob | NO | | NULL | || x509_subject | blob | NO | | NULL | || max_questions | int(11) unsigned | NO | | 0 | || max_updates | int(11) unsigned | NO | | 0 | || max_connections | int(11) unsigned | NO | | 0 | || max_user_connections | int(11) | NO | | 0 | || plugin | char(64) | NO | | | || authentication_string | text | NO | | NULL | |+------------------------+-----------------------------------+------+-----+---------+-------+42 rows in set (0.00 sec)MariaDB [mysql]> select * from user; ##查询user表中的所有内容（*可以用此表中的任何子段来代替）+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string |+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+| localhost | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | || 127.0.0.1 | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | || ::1 | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | |+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+3 rows in set (0.00 sec)MariaDB [mysql]> select Password from user; ##查询user表中的password内容+-------------------------------------------+| Password |+-------------------------------------------+| *84BB5DF4823DA319BBF86C99624479A198E6EEE9 || *84BB5DF4823DA319BBF86C99624479A198E6EEE9 || *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |+-------------------------------------------+2.数据库及表的建立
MariaDB [(none)]> create database westos; ##创建westos数据库MariaDB [westos]> create table linux( -> username varchar(15) not null, -> password varchar(50) not null); ##创建linux表，并且linux表中含有两个字段，username password具体代码块如下：[root@test ~]# mysql -uroot -pEnter password: Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 13Server version: 5.5.35-MariaDB MariaDB ServerCopyright (c) 2000, 2013, Oracle, Monty Program 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 |+--------------------+3 rows in set (0.00 sec)MariaDB [(none)]> create database westos; ##创建westos数据库Query OK, 1 row affected (0.00 sec)MariaDB [(none)]> show databases; ##显示数据库+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || westos |+--------------------+4 rows in set (0.01 sec)MariaDB [(none)]> use westos; ##进入westos数据库Database changedMariaDB [westos]> show tables; ##显示westos数据库中的表的名称Empty set (0.00 sec)MariaDB [westos]> create table linux( -> username varchar(15) not null, -> password varchar(50) not null); ##创建linux表，并且linux表中含有两个字段，username passwordQuery OK, 0 rows affected (0.04 sec)MariaDB [westos]> show tables; ##显示westos数据库中的表的名称+------------------+| Tables_in_westos |+------------------+| linux |+------------------+1 row in set (0.00 sec)MariaDB [westos]> desc linux; ##查询linux表的结构（显示所有字段的名称）+----------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| username | varchar(15) | NO | | NULL | || password | varchar(50) | NO | | NULL | |+----------+-------------+------+-----+---------+-------+2 rows in set (0.00 sec)3.更新数据库
MariaDB [westos]> insert into linux value ('user1','123'); ##向linux表中插入数据MariaDB [westos]> insert into linux values ('user2',password('123') ); ##向linux表中插入数据,密码为加密MariaDB [westos]> update linux set password=password('123') where password='123'; ##将密码为123的 用户密码加密 MariaDB [westos]> alter table linux add class varchar(20) not null; ##在linux表中添加一个class字段，字数为20，并且不能为空MariaDB [westos]> alter table linux add age varchar(20) not null; ##在linux表中添加一个age字段，字数为20，并且不能为空MariaDB [westos]> alter table linux drop age; ##在linux表中删除age字段MariaDB [westos]> alter table linux add date varchar(20) not null after password; ##在linux表中插入字段，并且在password字段之后MariaDB [westos]> delete from linux where username='user2'; 删除linux表中user2的信息具体代码块如下：MariaDB [westos]> insert into linux value ('user1','123'); ##向linux表中插入数据Query OK, 1 row affected (0.02 sec)MariaDB [westos]> insert into linux values ('user2',password('123') ); ##向linux表中插入数据,密码为加密Query OK, 1 row affected (0.03 sec)MariaDB [westos]> insert into linux values ('user3','123'); Query OK, 1 row affected (0.04 sec)MariaDB [westos]> select * from linux;+----------+-------------------------------------------+| username | password |+----------+-------------------------------------------+| user1 | 123 || user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 || user3 | 123 |+----------+-------------------------------------------+3 rows in set (0.00 sec)MariaDB [westos]> update linux set password=password('123') where password='123'; ##将密码为123的 用户密码加密Query OK, 2 rows affected (0.30 sec)Rows matched: 2 Changed: 2 Warnings: 0MariaDB [westos]> select * from linux;+----------+-------------------------------------------+| username | password |+----------+-------------------------------------------+| user1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 || user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 || user3 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |+----------+-------------------------------------------+3 rows in set (0.00 sec)MariaDB [westos]> alter table linux add class varchar(20) not null; ##在linux表中添加一个class字段，字数为20，并且不能为空Query OK, 3 rows affected (0.09 sec) Records: 3 Duplicates: 0 Warnings: 0MariaDB [westos]> select * from linux;+----------+-------------------------------------------+-------+| username | password | class |+----------+-------------------------------------------+-------+| user1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | || user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | || user3 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | |+----------+-------------------------------------------+-------+3 rows in set (0.00 sec)MariaDB [westos]> alter table linux add age varchar(20) not null; ##在linux表中添加一个age字段，字数为20，并且不能为空Query OK, 3 rows affected (0.14 sec) Records: 3 Duplicates: 0 Warnings: 0MariaDB [westos]> select * from linux;+----------+-------------------------------------------+-------+-----+| username | password | class | age |+----------+-------------------------------------------+-------+-----+| user1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | || user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | || user3 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | |+----------+-------------------------------------------+-------+-----+3 rows in set (0.00 sec)MariaDB [westos]> alter table linux drop age; ##在linux表中删除age字段Query OK, 3 rows affected (0.08 sec) Records: 3 Duplicates: 0 Warnings: 0MariaDB [westos]> select * from linux;+----------+-------------------------------------------+-------+| username | password | class |+----------+-------------------------------------------+-------+| user1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | || user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | || user3 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | |+----------+-------------------------------------------+-------+3 rows in set (0.00 sec)MariaDB [westos]> alter table linux add date varchar(20) not null after password; ##在linux表中插入字段，并且在password字段之后Query OK, 3 rows affected (0.08 sec) Records: 3 Duplicates: 0 Warnings: 0MariaDB [westos]> select * from linux;+----------+-------------------------------------------+------+-------+| username | password | date | class |+----------+-------------------------------------------+------+-------+| user1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | || user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | || user3 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | |+----------+-------------------------------------------+------+-------+3 rows in set (0.00 sec)MariaDB [westos]> delete from linux where username='user2'; 删除linux表中user2的信息Query OK, 1 row affected (0.04 sec)MariaDB [westos]> select * from linux;+----------+-------------------------------------------+------+-------+| username | password | date | class |+----------+-------------------------------------------+------+-------+| user1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | || user3 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | |+----------+-------------------------------------------+------+-------+2 rows in set (0.00 sec)4.数据库备份
[root@test ~]# mysqldump -uroot -predhat --all-database ##备份所有表中的所有数据[root@test ~]# mysqldump -uroot -predhat --all-database --no-data ##备份所有表，但不备份数据[root@test ~]# mysqldump -uroot -predhat westos ##备份westos数据库[root@test ~]# mysqldump -uroot -predhat westos linux ##备份westos数据库中的linux表[root@test ~]# touch /mnt/westos.sql[root@test ~]# mysqldump -uroot -predhat westos > /mnt/westos.sql 备份westos数据库并将数据保存到/mnt/下当westos库缺少时，将备份的内容倒入数据库里边时，需要先建立westos数据库[root@test ~]# mysql -uroot -predhat -e "create database westos;" ##创建westos库[root@test ~]# mysql -uroot -predhat westos < /mnt/westos.sql ##将数据导入westos数据库由于备份数据库内容过多，这里就不粘贴具体的内容了5.数据库删除
MariaDB [westos]>delete from westos where username='user1'; ##删除user1的数据MariaDB [westos]> drop table linux; ##删除linux表MariaDB [westos]> drop database westos; ##删除westos库6.用户授权
MariaDB [(none)]> create user white@localhost identified by 'white'; ##创建white用户，只允许本机登陆MariaDB [(none)]> create user white@'%' identified by 'white'; ##创建white用户，表示white用户可以通过网络登录MariaDB [mysql]> select User,Host from user; ##查询用户信息MariaDB [mysql]> grant insert,update,delete,select on westos.linux to white@localhost; ##用户授权MariaDB [mysql]> show grants for white@localhost; ## 查看用户权力MariaDB [mysql]> grant select on westos.linux to white@'%'; ##用户授权MariaDB [mysql]> show grants for white@'%'; ##查看用户权力MariaDB [mysql]> revoke delete on westos.linux from white@localhost; ##去除用户权力MariaDB [mysql]> show grants for white@localhost; ##查看用户权力MariaDB [mysql]> drop user white@'%'; ##删除用户具体代码块如下：[root@test ~]# mysql -uroot -predhat Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 23Server version: 5.5.35-MariaDB MariaDB ServerCopyright (c) 2000, 2013, Oracle, Monty Program Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> create user white@localhost identified by 'white'; ##创建white用户，只允许本机登陆Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> create user white@'%' identified by 'white'; ##创建white用户，表示white用户可以通过网络登录Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> use mysql;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedMariaDB [mysql]> select User,Host from user; ##查询用户信息+-------+-----------+| User | Host |+-------+-----------+| white | % || root | 127.0.0.1 || root | ::1 || root | localhost || white | localhost |+-------+-----------+5 rows in set (0.00 sec)MariaDB [mysql]> grant insert,update,delete,select on westos.linux to white@localhost; ##用户授权Query OK, 0 rows affected (0.00 sec)MariaDB [mysql]> show grants for white@localhost; ##查看用户权力+--------------------------------------------------------------------------------------------------------------+| Grants for white@localhost |+--------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'white'@'localhost' IDENTIFIED BY PASSWORD '*F04D4C1A151E815957AFBA11C4FEBE241D1E2AF6' || GRANT SELECT, INSERT, UPDATE, DELETE ON `westos`.`linux` TO 'white'@'localhost' |+--------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)MariaDB [mysql]> grant select on westos.linux to white@'%'; ##用户授权Query OK, 0 rows affected (0.00 sec)MariaDB [mysql]> show grants for white@'%'; ##查看用户权力+------------------------------------------------------------------------------------------------------+| Grants for white@% |+------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'white'@'%' IDENTIFIED BY PASSWORD '*F04D4C1A151E815957AFBA11C4FEBE241D1E2AF6' || GRANT SELECT ON `westos`.`linux` TO 'white'@'%' |+------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)MariaDB [mysql]> revoke delete on westos.linux from white@localhost; ##去除用户权力Query OK, 0 rows affected (0.00 sec)MariaDB [mysql]> show grants for white@localhost; ##查看用户权力+--------------------------------------------------------------------------------------------------------------+| Grants for white@localhost |+--------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'white'@'localhost' IDENTIFIED BY PASSWORD '*F04D4C1A151E815957AFBA11C4FEBE241D1E2AF6' || GRANT SELECT, INSERT, UPDATE ON `westos`.`linux` TO 'white'@'localhost' |+--------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)MariaDB [mysql]> drop user white@'%'; ##删除用户Query OK, 0 rows affected (0.00 sec)测试一下：[root@test ~]# vim /etc/my.cnfskip-networking=0[root@test ~]# systemctl restart mariadb.service [root@test ~]# mysql -uwhite -pwhite -h 172.25.254.10Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 12Server version: 5.5.35-MariaDB MariaDB ServerCopyright (c) 2000, 2013, Oracle, Monty Program Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> 7.密码修改
已知密码的情况：[root@test ~]# mysqladmin -uroot -predhat password westos ##更改密码具体代码块如下：[root@test ~]# mysqladmin -uroot -predhat password westos ##更改密码[root@test ~]# mysql -uroot -pwestos ##登陆Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 6Server version: 5.5.35-MariaDB MariaDB ServerCopyright (c) 2000, 2013, Oracle, Monty Program Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> quitBye不知道密码的情况下：[root@test ~]# systemctl stop mariadb ##关闭mysql[root@test ~]# mysqld_safe --skip-grant-tables & ##开启mysql登陆接口并忽略授权表[root@test ~]# mysql ##直接不用密码可以登陆MariaDB [(none)]> update mysql.user set Password=password('123') where User='root'; ##更改密码MariaDB [(none)]> quit[root@test ~]# ps aux | grep mysql ##过滤mysql的所有进程[root@test ~]# kill -9 9334 ##结束进程[root@test ~]# kill -9 9489 ##结束进程[root@test ~]# systemctl start mariadb ##重启mariadb[root@test ~]# mysql -uroot -p123 ##登陆MariaDB [(none)]> quit具体代码块如下：[root@test ~]# systemctl stop mariadb ##关闭mysql[root@test ~]# mysqld_safe --skip-grant-tables & ##开启mysql登陆接口并忽略授权表[1] 9334[root@test ~]# 170513 10:18:23 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.170513 10:18:23 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql^C ##ctrl+c[root@test ~]# mysql ##直接不用密码可以登陆Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 1Server version: 5.5.35-MariaDB MariaDB ServerCopyright (c) 2000, 2013, Oracle, Monty Program Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> update mysql.user set Password=password('123') where User='root'; ##更改密码Query OK, 3 rows affected (0.00 sec)Rows matched: 3 Changed: 3 Warnings: 0MariaDB [(none)]> quit[root@test ~]# ps aux | grep mysql ##过滤mysql的所有进程root 9334 0.0 0.0 113252 1568 pts/0 S 10:18 0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tablesmysql 9489 0.0 4.6 859068 87592 pts/0 Sl 10:18 0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sockroot 9543 0.0 0.0 112640 936 pts/0 R+ 10:19 0:00 grep --color=auto mysql[root@test ~]# kill -9 9334 ##结束进程[root@test ~]# kill -9 9489 ##结束进程[1]+ Killed mysqld_safe --skip-grant-tables[root@test ~]# ps aux | grep mysql ##过滤mysql的所有进程root 9570 0.0 0.0 112640 936 pts/0 R+ 10:20 0:00 grep --color=auto mysql[root@test ~]# systemctl start mariadb ##重启mariadb[root@test ~]# mysql -uroot -p123 ##登陆Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 2Server version: 5.5.35-MariaDB MariaDB ServerCopyright (c) 2000, 2013, Oracle, Monty Program Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> quitBye8.网络数据库
1.install software[root@desktop ~]# yum install httpd php php-mysql -y ##安装php 及php-mysql[root@desktop ~]# systemctl start httpd ##开启httpd服务[root@desktop ~]# systemctl enable httpd ##开机自动开启httpd服务[root@desktop ~]# systemctl stop firewalld ##关闭防火墙[root@desktop ~]# systemctl disable firewalld ##开机自动关闭防火墙因为楼主已经有 phpMyAdmin-3.4.0-all-languages.tar.bz2这个包了，所以直接解压就行[root@test ~]# cd /mnt/ ##目录下边已有php压缩包[root@test mnt]# lsKwestoskey.+157+43758.key phpMyAdmin-3.4.0-all-languages.tar.bz2Kwestoskey.+157+43758.private westos.sql[root@desktop ~]# tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2 -C /var/www/html ##解压包到/var/www/html下[root@desktop ~]# cd /var/www/html/[root@desktop html]# lsphpMyAdmin-3.4.0-all-languages[root@desktop html]# mv phpMyAdmin-3.4.0-all-languages mysqladmin ##重命名[root@desktop html]# cd mysqladmin/[root@desktop mysqladmin]# cp -p config.sample.inc.php config.inc.php ##复制模板建立配置文件[root@desktop mysqladmin]# vim config.inc.php ##更改配置文件17 $cfg['blowfish_secret'] = 'mysql'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */[root@desktop mysqladmin]# systemctl restart httpd ##重启httpd服务2.测试
http://172.25.254.10/mysqladmin 
1 0
- Mariadb数据库配置及管理
- Mariadb数据库的部署及管理
- Mariadb数据库：基本语句及操作 数据库及用户管理
- MariaDB数据库管理系统
- mariadb数据库管理系统
- Linux-mariadb数据库管理
- Linux学习笔记--MariaDB数据库配置与管理
- MariaDB数据库安装、配置
- mariadb数据库安装配置
- MariaDB数据库的安装配置及常用操作
- Mariadb数据库的性能优化及安全性配置
- Centos 7 MariaDB 数据库服务安装及配置
- 使用MariaDB数据库管理系统。
- 配置phpMyAdmin管理MySQL/MariaDB
- MariaDB安装及字符集配置
- CentOS7yum安装mariadb及配置
- mariadb数据库的搭建与管理
- linux初学者-数据库管理MariaDB篇
- 我所理解的多线程之synchronized
- Dbutils简介
- Android触摸事件分发机制完全解析《一》
- 腾讯云搭建git服务器--和踩过的坑
- 12 qt的反射机制应用
- Mariadb数据库配置及管理
- C语言中局部变量与全局变量在内存中的存放位置
- Lintcode50 Product of Array Exclude Itself solution 题解
- 安卓中的Flux架构
- 手指上的代码，手心里的作品
- 【C++学习笔记】如何输入含有未知变量的数组？
- SpringMVC 拦截器
- 【阿里云服务器】外网无法访问tomcat下部署的项目
- 4-数据类型及相关的操作
