树莓派4 (4)给pi4安装数据库MySql

安装

//运行命令
$ sudo apt-get update
$ sudo apt-get install mariadb-server-10.3

配置—设置密码

pi@raspberrypi:~ $ mysql -uroot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 10.0.28-MariaDB-2+b1 Raspbian testing-staging
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>use mysql;
MariaDB [mysql]> update user set plugin='mysql_native_password' where user='root';
MariaDB [mysql]> UPDATE user SET password=PASSWORD('你想要设定的密码') WHERE user='root';
MariaDB [mysql]> flush privileges;
MariaDB [mysql]> exit;

重启 MySql

sudo service mysql restart

数据库操作

pi@raspberrypi:~ $ mysql -uroot -p上边设置的密码
MariaDB [none]>CREATE DATABASE 数据库名;
//带参数创建
MariaDB [none]>CREATE DATABASE IF NOT EXISTS 数据库名 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
//显示所有数据库
MariaDB [(none)]> show databases;
//选择数据库
MariaDB [(none)]> use mysql;
//删除数据库
MariaDB [(none)]> drop database 数据库名;

表操作

//创建表
MariaDB [mysql]>CREATE TABLE 表名(
   -> id INT NOT NULL AUTO_INCREMENT,
   -> title VARCHAR(100) NOT NULL,
   -> author VARCHAR(40) NOT NULL,
   -> date DATE,
   -> PRIMARY KEY ( id )
   -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
   //删除表
MariaDB [mysql]>DROP TABLE 表名 ;

开启mysql远程访问

mysql -u root -p
mysql>use mysql;
mysql>update user set host = '%' where user = 'root';
mysql>select host, user from user;
mysql>flush privileges;

pi@raspberrypi:~ $ sudo nano /etc/mysql/my.cnf

文件尾部新增内容，保存退出

重启mysql服务

pi@raspberrypi:~ $ sudo killall mysqld
 pi@raspberrypi:~ $ sudo service mysql start

确认端口是否打开

远程工具连接测试