如果觉得对您有所帮助,点个赞吧
公司需要,一些设备软件迁到Linux平台上,数据库由sqlserver转成mysql。这边记录一下使用过程。
Linux发行版本:ubuntu18.04
一、安装MySQL
1.安装
现在没有必要非要上官网下载tar包,直接根据自己需要选择安装server还是client,如果只做服务器的话,实际安装server就可以了。
sudo apt-get install mysql-server //服务端
sudo apt-get install mysql-client //客户端
sudo apt-get install libmysqlclient-dev //程序编译时链接的库
2.安装过程
图是借的,侵删~
安装过程中会出现这个界面,让你输入mysql的root用户密码,如果没有出现到第四步。
3.安装完成
安装完成之后dpkg命令查看状态
dpkg -l | grep mysql
显示如下说明安装成功:
aaron@aaron-Raytine-PC:~$ dpkg -l | grep mysql
ii libmysqlclient-dev 5.7.30-0ubuntu0.18.04.1 amd64 MySQL database development files
ii libmysqlclient20:amd64 5.7.30-0ubuntu0.18.04.1 amd64 MySQL database client library
ii libqt5sql5-mysql:amd64 5.9.5+dfsg-0ubuntu2.5 amd64 Qt 5 MySQL database driver
ii mysql-client 5.7.30-0ubuntu0.18.04.1 all MySQL database client (metapackage depending on the latest version)
ii mysql-client-5.7 5.7.30-0ubuntu0.18.04.1 amd64 MySQL database client binaries
ii mysql-client-core-5.7 5.7.30-0ubuntu0.18.04.1 amd64 MySQL database core client binaries
ii mysql-common 5.8+1.0.4 all MySQL database common files, e.g. /etc/mysql/my.cnf
ii mysql-server 5.7.30-0ubuntu0.18.04.1 all MySQL database server (metapackage depending on the latest version)
ii mysql-server-5.7 5.7.30-0ubuntu0.18.04.1 amd64 MySQL database server binaries and system database setup
ii mysql-server-core-5.7 5.7.30-0ubuntu0.18.04.1 amd64 MySQL database server binaries
使用如下命令启动mysql
service mysql start
启动后使用如下命令查看状态
sudo netstat -anp | grep mysql
显示如下说明启动成功:
aaron@aaron-Raytine-PC:~$ sudo netstat -anp | grep mysql
[sudo] aaron 的密码:
tcp6 0 0 :::3306 ::😗 LISTEN 935/mysqld
unix 2 [ ACC ] 流 LISTENING 29838 935/mysqld /var/run/mysqld/mysqld.sock
unix 3 [ ] 流 已连接 45986 3419/mysql
unix 3 [ ] 流 已连接 49244 935/mysqld /var/run/mysqld/mysqld.sock
使用命令进行登陆
mysql -u root -p
输入密码,进行登陆,如果之前没有设置密码请看第四步
aaron@aaron-Raytine-PC:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.30-0ubuntu0.18.04.1 (Ubuntu)
Copyright © 2000, 2020, 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>
4.安装后无法登陆
使用mysql -u root -p命令但是不知道root密码是应为之前没有设置,这个时候进行环境配置,设置root密码。
首先,进行忽略密码登陆,打开配置文件
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
在最后加入skip-grant-tables
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
skip-grant-tables
-- 插入 --
保存退出
重新登陆
mysql -u mysql
登陆后切换database
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
修改密码
update user set authentication_string=PASSWORD(“新密码”) where user=‘root’;
修改user表字段,设置为mysql_native_password为localhost
update user set plugin=“localhost”;
查看一下用户
select user,host from mysql.user;
显示如下正确:
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| debian-sys-maint | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)
然后进行刷新
flush privileges;
退出quit
重启sudo service mysql restart
这样应该就可以登陆进去了。
二、Qt连接MySQL
Qt版本:5.13.2
首先附上测试代码,此时应该是连接不上的,在编译阶段就会报错
#include <QPluginLoader>
#include <QSqlError>
#include <QSqlDatabase>
#include <QDebug>
#include <QCoreApplication>
void loadMySqlDriver();
int main(int argc, char *argv[])
{
QCoreApplication a(argc, argv);
/*
*链接mysql数据库
*/
QSqlDatabase db( QSqlDatabase::addDatabase( "QMYSQL" ) );
db.setHostName("127.0.0.1");
db.setDatabaseName("contect_test");
db.setUserName("root");
db.setPassword("root");
db.setPort(3360);
if(!db.open())/*测试数据库是否链接成功*/
{
qDebug()<<db.lastError()<<endl;
}
else
{
qDebug()<<"Ok"<<endl;
}
return a.exec();
}
此时编译可能会报错:
QSqlDatabase: QMYSQL driver not loaded
QSqlDatabase: available drivers: QSQLITE QMYSQL QMYSQL QMYSQL3 QODBC
Qt虽然支持直接连接MySQL,但是并没有将插件直接编译好,是因为MySQL版本不一样,编译出来的插件也不一样,这就是为什么CSDN下载的驱动是不可以用的原因。
编译驱动方法
首先在安装qt的时候一定要安装源码
1.安装依赖包
sudo apt-get install openssl
sudo apt-get install libssl-dev
2.修改mysql.pro文件
该文件在qt安装目录下,src中,别找错了,qt源码目录有几个src,在我的ubuntu上,是这个路径
software/qt/5.13.2/Src/qtbase/src/plugins/sqldrivers/mysql
打开pro文件,将QMAKE_USE注释掉
TARGET = qsqlmysql
HEADERS += $$PWD/qsql_mysql_p.h
SOURCES += $$PWD/qsql_mysql.cpp $$PWD/main.cpp
#QMAKE_USE += mysql
OTHER_FILES += mysql.json
PLUGIN_CLASS_NAME = QMYSQLDriverPlugin
include(../qsqldriverbase.pri)
3.修改qsqldriverbase.pri文件
返回到上层目录
找到qsqldriverbase.pri文件,打开
将include($$shadowed($$PWD)/qtsqldrivers-config.pri)
注释掉并添加
include($$shadowed($$PWD)/configure.pri)
QT = core core-private sql-private
# For QMAKE_USE in the parent projects.
#include($$shadowed($$PWD)/qtsqldrivers-config.pri)
include($$shadowed($$PWD)/configure.pri)
PLUGIN_TYPE = sqldrivers
load(qt_plugin)
DEFINES += QT_NO_CAST_TO_ASCII QT_NO_CAST_FROM_ASCII
4.进行qmake
/qt安装目录/Qt5.13.2/gcc_64/bin/qmake "INCLUDEPATH+=/usr/include/mysql" "LIBS+=-L/usr/lib/x86_64-linux-gnu/ -lmysqlclient" mysql.pro
qmake生成Makefile文件
sudo make
5.make完成后
在/Qt安装目录software/qt/5.13.2/src/qtbase/src/plugins/sqldrivers/plugins/sqldrivers/
目录下会出现libqsqlmysql.so.debug
和 libqsqlmysql.so
两个文件,把它们拷贝到/gcc_64/plugins/sqldrivers覆盖原文件。