Ubuntu下MySQL数据库安装、配置，以及C++调用方式

最新推荐文章于 2024-06-01 19:05:32 发布

拉普拉斯不想变

最新推荐文章于 2024-06-01 19:05:32 发布

阅读量1.8k

点赞数 4

文章标签：数据库 ubuntu mysql

本文链接：https://blog.csdn.net/m0_52499244/article/details/131129776

版权

今天配置mysql时碰到很多问题，也有一些教程由于版本更新，有些指令已经不正确了，因此记录一下，作为学习笔记。

1.安装mysql

sudo apt-get install mysql-server
sudo apt-get isntall mysql-client
sudo apt-get install libmysqlclient-dev

2.启动服务、进入数据库

name@pc-name:~$ sudo service mysql start
[sudo] name 的密码： 
name@pc-name:~$ sudo mysql -u root -p'password'
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 61
Server version: 8.0.33-0ubuntu0.22.04.2 (Ubuntu)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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>

看到welcome就是进去了，如果进不去，建议改配置文件

sudo vim /etc/mysql/my.cnf

增加下面两句话（vim使用: i进入插入模式，ESE退出插入模式，:wq保存更改并退出）

# The MySQL database server configuration file.
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
[mysqld]#add1

skip-grant-tables#add2
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

sudo service mysql restart
sudo mysql -u root -p
[sudo] cheng 的密码： #直接回车

这样就可以免密码验证进入mysql了，之后就可以对密码进行更改；
以上参考Ubuntu下安装MySQL 以及C++连接MySQL
配置密码更改后可以更改回来。

Error1：

增加了skip-grant-tables策略会遇到报错；

mysql> alter user 'root'@'localhost' identified by '123';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

解决方法：

mysql> flush privileges;alter user 'root'@'localhost' identified by '123';

注：在mysql>下指令要分号结尾

Error2：

更改密码命令，MySQL5.6.6版本之后增加了密码强度验证插件validate_password，相关参数设置的较为严格。会遇到error。

mysql> flush privileges;alter user 'root'@'localhost' identified by '123';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

（此处修改密码语句已经更换为新版mysql，新版的mysql不支持password函数了）

解决办法：

1.安装validate_password；
2.查询密码策略；

mysql> INSTALL COMPONENT 'file://component_validate_password';
Query OK, 0 rows affected (0.21 sec)
 
mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set, 1 warning (0.00 sec)

3.更改策略；

mysql> set global validate_password.policy=0;
Query OK, 0 rows affected (0.05 sec)
mysql> set global validate_password.mixed_case_count=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password.number_count=3;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password.special_char_count=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password.length=3;
Query OK, 0 rows affected (0.00 sec)
 
mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password.dictionary_file    |       |
| validate_password.length             | 3     |
| validate_password.mixed_case_count   | 0     |
| validate_password.mumber_count       | 3     |
| validate_password.policy             | LOW   |
| validate_password.special_char_count | 0     |
+--------------------------------------+-------+
6 rows in set (0.00 sec)

以上参考mysql5.7设置简单密码报错ERROR 1819 (HY000): Your password does not satisfy the current policy requirements，但是他的文章中命令是旧版的，很多已经变了。
这样策略更改后就可以愉快的改密码了。修改完restart以下服务。

c++测试程序：

#include <stdio.h>
#include <iostream>
#include <mysql.h>
using namespace std;
int main(int argc,char *argv[])
{
    MYSQL conn;
    int res;
    mysql_init(&conn);
    //"root":数据库管理员 "123":root密码 "test":数据库的名字
    if(mysql_real_connect(&conn, "localhost","root","123","mysql",0,NULL,CLIENT_FOUND_ROWS))
    {
        cout << "connect success" << endl;
    }else
    {
        cout << "connect failed" << endl;
        cout<<mysql_error(&conn);
    }
    return 0;
}

我碰到一个无法连接的问题，后来发现是/etc/mysql/mysql.conf.d的port注释了，我还把#bind-address = 127.0.0.1、#mysqlx-bind-address = 127.0.0.1，这两行注释掉，能正常调用了

# mysql.conf.d
# The MySQL database server configuration file.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld]
#
# * Basic Settings
#
user		= mysql
# pid-file	= /var/run/mysqld/mysqld.pid
# socket	= /var/run/mysqld/mysqld.sock
port		= 3306
# datadir	= /var/lib/mysql


# If MySQL is running as a replication slave, this should be
# changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir
# tmpdir		= /tmp
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address		= 127.0.0.1
#mysqlx-bind-address	= 127.0.0.1

有其他报错可以根据报错内容逐一排查。

connect failed
Unknown database 'xxx'

这个报错只是没有此数据库，不用担心，可以新建一个，也可以使用下面这个程序测试

#include <iostream>
#include <mysql/mysql.h>

using namespace std;
///
/// \brief sql_create 创建数据库
///
void sql_create()
{
    MYSQL mysql;
    mysql_init(&mysql);
    if(!mysql_real_connect(&mysql, "localhost", "root", "123", "mysql", 3306, NULL, 0)) {
        cout<<"mysql connect error: "<<mysql_error(&mysql)<<" "<<mysql_errno(&mysql)<<endl;
    }

    string str = "create database school;";

    mysql_real_query(&mysql, str.c_str(), str.size());

    str = "alter database school charset=utf8;";

    mysql_real_query(&mysql, str.c_str(), str.size());
// 自动生成id add数据时去掉id
//    str = "create table school.students(id int(10) primary key auto_increment, name varchar(20) not null, age int(3) not null);";
//手动添加id
    str = "create table school.students(id int(10) primary key , name varchar(20) not null, age int(3) not null);";

    mysql_real_query(&mysql, str.c_str(), str.size());

    mysql_close(&mysql);
}
///
/// \brief sql_add 增加数据
///
void sql_add()
{
    MYSQL mysql;
    mysql_init(&mysql);
    mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, "utf8");
    mysql_options(&mysql, MYSQL_INIT_COMMAND, "SET NAMES utf8");

    if(!mysql_real_connect(&mysql, "localhost", "root", "123", "school", 3306, NULL, 0)) {
        cout<<"mysql connect error: "<<mysql_error(&mysql)<<" "<<mysql_errno(&mysql);
    }

    string str = "insert into students(id, name, age) values(1, \'张三\', 20)";
    mysql_real_query(&mysql, str.c_str(), str.size());
    str = "insert into students(id, name, age) values(2, \'李四\', 18)";
    mysql_real_query(&mysql, str.c_str(), str.size());
    str = "insert into students(id, name, age) values(3, \'王五\', 19)";
    mysql_real_query(&mysql, str.c_str(), str.size());
    str = "insert into students(id, name, age) values(4, \'赵六\', 21)";
    mysql_real_query(&mysql, str.c_str(), str.size());
    str = "insert into students(id, name, age) values(5, \'马七\', 20)";
    mysql_real_query(&mysql, str.c_str(), str.size());

    mysql_close(&mysql);
}
///
/// \brief sql_modify 修改数据
///
void sql_modify()
{
    MYSQL mysql;
    mysql_init(&mysql);
    mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, "utf8");
    mysql_options(&mysql, MYSQL_INIT_COMMAND, "SET NAMES utf8");

    if(!mysql_real_connect(&mysql, "localhost", "root", "123", "school", 3306, NULL, 0)) {
        cout<<"mysql connect error: "<<mysql_error(&mysql)<<" "<<mysql_errno(&mysql);
    }

    string str = "update students set age = 30 where name = \'王五\'";

    mysql_real_query(&mysql, str.c_str(), str.size());

    mysql_close(&mysql);
}
///
/// \brief sql_delete 删除数据
///
void sql_delete()
{
    MYSQL mysql;
    mysql_init(&mysql);
    mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, "utf8");
    mysql_options(&mysql, MYSQL_INIT_COMMAND, "SET NAMES utf8");

    if(!mysql_real_connect(&mysql, "localhost", "root", "123", "school", 3306, NULL, 0)) {
        cout<<"mysql connect error: "<<mysql_error(&mysql)<<" "<<mysql_errno(&mysql);
    }

    string str = "delete from students where name = \'赵六\'";

    mysql_real_query(&mysql, str.c_str(), str.size());

    mysql_close(&mysql);
}
///
/// \brief sql_query 查询数据
///
void sql_query()
{
    MYSQL mysql;
    mysql_init(&mysql);
    mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, "utf8");
    mysql_options(&mysql, MYSQL_INIT_COMMAND, "SET NAMES utf8");

    if(!mysql_real_connect(&mysql, "localhost", "root", "123", "school", 3306, NULL, 0)) {
        cout<<"mysql connect error: "<<mysql_error(&mysql)<<" "<<mysql_errno(&mysql);
    }

    string str = "select * from students;";

    mysql_real_query(&mysql, str.c_str(), str.size());

    MYSQL_RES *result = mysql_store_result(&mysql);

    MYSQL_ROW row;
    while (row = mysql_fetch_row(result)) {
        cout<<"id: "<<row[0]<<" name: "<<row[1]<<" age: "<<row[2]<<endl;
    }

    mysql_free_result(result);

    mysql_close(&mysql);
}

int main()
{
    cout << "Hello World!111" << endl;
    sql_create();
    cout << "Hello World!222" << endl;
    sql_add();
    cout << "Hello World!333" << endl;
    sql_query();
    cout << "Hello World!444" << endl;
    sql_modify();
    cout << "Hello World!555" << endl;
    sql_query();
    cout << "Hello World!666" << endl;
    sql_delete();
    cout << "Hello World!777" << endl;
    sql_query();

    return 0;
}

输出：
Hello World!111
Hello World!222
Hello World!333
id: 1 name: 张三 age: 20
id: 2 name: 李四 age: 18
id: 3 name: 王五 age: 30
id: 4 name: 赵六 age: 21
id: 5 name: 马七 age: 20
Hello World!444
Hello World!555
id: 1 name: 张三 age: 20
id: 2 name: 李四 age: 18
id: 3 name: 王五 age: 30
id: 4 name: 赵六 age: 21
id: 5 name: 马七 age: 20
Hello World!666
Hello World!777
id: 1 name: 张三 age: 20
id: 2 name: 李四 age: 18
id: 3 name: 王五 age: 30
id: 5 name: 马七 age: 20

进程已结束,退出代码0

此代码来源于Ubuntu c++ MySQL数据库操作
到这里就可以快乐使用mysql了。由于管理不便，可以安装一个界面管理DBeaver

wget https://dbeaver.io/files/dbeaver-ce_latest_amd64.deb
sudo dpkg -i dbeaver-ce_latest_amd64.deb

在这里插入图片描述

拉普拉斯不想变

关注

4
点赞
踩
10

收藏

觉得还不错? 一键收藏
1
评论
Ubuntu下MySQL数据库安装、配置，以及C++调用方式

今天配置mysql时碰到很多问题，也有一些教程由于版本更新，有些指令已经不正确了，因此记录一下，作为学习笔记。
复制链接

扫一扫