mysql应用
1. 简述
MySQL是开源的关系型数据库。官网：https://dev.mysql.com/。常用的操作工具是navicat（商业版，需要破解）。
2. 安装及应用
可通过https://dev.mysql.com/downloads/下载MySQL社区版。以64位win7系统为例，下载最新的8.0.17版本zip包：https://dev.mysql.com/downloads/mysql/
直接解压到系统D盘即可，https://dev.mysql.com/doc/refman/8.0/en/windows-install-archive.html。参考https://www.runoob.com/mysql/mysql-install.html配置：
在解压目录下编辑my.ini配置文件：
[client] # 设置mysql客户端默认字符集 default-character-set=utf8 [mysqld] # 设置3306端口 port = 3306 # 设置mysql的安装目录 basedir=D:\\mysql-8.0.17-winx64 # 设置 mysql数据库的数据的存放目录，MySQL 8+ 不需要以下配置，系统自己生成即可，否则有可能报错 # datadir=D:\\web\\sqldata # 允许最大连接数 max_connections=20 # 服务端使用的字符集默认为8比特编码的latin1字符集 character-set-server=utf8 # 创建新表时将使用的默认存储引擎 default-storage-engine=INNODB
初始化数据库
初始化数据库： mysqld --initialize --console 执行完成后，会输出 root 用户的初始默认密码，如： ... 2018-04-20T02:35:05.464644Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: APWCY5ws&hjQ ... APWCY5ws&hjQ 就是初始密码，后续登录需要用到，你也可以在登陆后修改密码。 输入以下安装命令： mysqld install 启动输入以下命令即可： net start mysql
centos7安装
yum list installed mysql* 列出后如果有就删除 sudo yum remove mysql-community-* rm -rf /var/lib/mysql rm /etc/my.cnf 安装源 wget https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm sudo yum instal mysql80-community-release-el7-1.noarch.rpm 配置mysql版本 sudo vim /etc/yum.repos.d/mysql-community.repo enabled 为1表示启用，将要安装的版本的enabled改为1后保存 目前使能mysql5.7，暂不使用mysql8.0 yum install mysql-server 安装mysql-community-server、mysql-community-client、mysql-community-common、mysql-community-libs
权限设置
chown mysql:mysql -R /var/lib/mysql*
初始化
mysqld --initialize
管理账号grep 'temporary password' /var/log/mysqld.log //找到默认密码mysql -uroot -pset password for 'root'@'localhost'=password('NEWPASSWORD'); 或者ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEWPASSWORD';// 修改密码,注意密码要复杂一些，否则会不能通过。
ubuntu18.04安装
# dpkg -l | grep mysql #查看有没有安装Mysql # sudo apt install mysql-server # systemctl enable mysql.service #默认情况下，mysql已启动，并开机自启 # sudo mysql -u root -p #默认情况下mysql没有密码，输入系统密码后就可进入
为了确保数据库的安全性和正常运转，对数据库进行初始化操作。这个初始化操作涉及下面5个步骤。
（1）安装验证密码插件。
（2）设置root管理员在数据库中的专有密码。
（3）随后删除匿名账户，并使用root管理员从远程登录数据库，以确保数据库上运行的业务的安全性。
（4）删除默认的测试数据库，取消测试数据库的一系列访问权限。
（5）刷新授权列表，让初始化的设定立即生效。
运行命令sudo mysql_secure_installation，根据提示操作。
mysql命令删除sudo
在ubuntu上使用apt-get安装mysql server之后，每次只能用sudo连接，mysql默认使用auth_socket_plugin进行认证：
If you install 5.7 and don’t provide a password to the root user, it will use the auth_socket plugin. That plugin doesn’t care and doesn’t need a password. It just checks if the user is connecting using a UNIX socket and then compares the username.
解决方法是使用mysql自带密码认证功能：
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '12345678';
等价于
update mysql.user set authentication_string=PASSWORD('12345678'), plugin='mysql_native_password' where user='root';
flush privileges;
其中，%是user表的Host字段，12345678为新设置的密码。完成后需要重启mysql服务。
配置编码为utf8和mysql用户，/etc/my.cnf
[client] default-character-set=utf8 # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html [mysqld] user=mysql character-set-server=utf8 collation-server=utf8_general_ci # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
启动数据库
mysqld --console
关闭数据库
mysqladmin -u root shutdown
登录数据库
mysql -h 主机名 -u 用户名 -p
修改密码
忘记初始化密码参考：https://www.cnblogs.com/qianzf/p/7089197.html，命令：
mysqld --defaults-file="../my.ini" --skip-grant-tables --shared-memory --console
update mysql.user set authentication_string="" where user="root";
flush privileges;
ALTER USER USER() IDENTIFIED BY 'wang';
允许远程访问
use mysql;
update user set host = '%' where user = 'root';
flush privileges;
备份恢复数据
a、导出整个数据库(包括数据库中的数据） mysqldump -u username -p dbname > dbname.sql b、导出数据库结构（不含数据） mysqldump -u username -p -d dbname > dbname.sql c、导出数据库中的某张数据表（包含数据） mysqldump -u username -p dbname tablename > tablename.sql d、导出数据库中的某张数据表的表结构（不含数据） mysqldump -u username -p -d dbname tablename > tablename.sql mysql -u<username> -p<password> <dbname> < /opt/mytest_bak.sql #库必须存在，空库也可
shell命令
status
show databases;
use database_name;
show tables;
desc table_name; // describe table
show columns from table;
show index from table;
show table status from database; 显示数据库中所有表的信息
show table status form database_name like "user%"; 表名以user开头的表的信息
show processlist; 显示连接
show full processlist; 显示所有连接
kill id; 断开连接，其中id为show processlist中的id
sql语句
create database dbtest；
CREATE DATABASE IF NOT EXISTS RUNOOB DEFAULT CHARSET utf8 COLLATE utf8_general_ci; //如果数据库不存在则创建，存在则不创建，并设定编码集为utf8
create table tabletest (id int not NULL auto_increment, inserttime DATE, PRIMARY KEY(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into tabletest (inserttime) values ('2019-9-3 13:00:30');
delete from elec_data where id<12340;
alter table tabletest change inserttime insert_time DATETIME;
alter table tabletest ADD ui1 INT, ADD ui2 INT;
3. C API
C++库依赖boost库，比较复杂，一般直接使用C客户端在C/C++应用中。
3.1 版本说明
Connector/C is a client library that implements the C API for client/server communication.
MySQL C API有两个版本：libmysqlclient和libmysqld。两个版本有相同接口。
libmysqlclient用于网络通信连接。
libmysqld是嵌入式版本库，用于程序本身包含MySQL server的应用。
在windows下，网络版静态库为mysqlclient.lib，动态库是libmysql.dll，动态导入静态库为libmysql.lib。嵌入式版本，相应的库名为：mysqlserver.lib,libmysqld.dll,和libmysqld.lib.
两种方式获取C API头文件和库：
1） MySQL Server distribution，包含libmysqlclient和libmysqld。
2） Connector/C distribution，仅仅包含libmysqlclient。下载地址https://downloads.mysql.com/archives/c-c/。
3.2 C API调用流程
1） 调用mysql_library_init()初始化MySQL客户端库。
2） 调用mysql_init()初始化一个连接句柄（handler），通过mysql_real_connect()连接serve。
3） 调用SQL语句并处理结果。
4） 调用mysql_close()关闭连接。
5） 调用mysql_library_end()结束MySQL client库的使用。
使用注意点如下：
1） mysql_library_init()和mysql_library_end()提供隐藏的内存管理，对于embedded server相关应用还包含启动和停止server的调用。在非多线程环境下，mysql_library_init()可以省略，因为mysql_init()会动态调用它。
2） 一旦连接成功，mysql_real_connect()会设置reconnect flag为0。reconnect为1表示会重新连接server，当因为连接断开而执行statement失败时。可通过调用mysql_options()设置MYSQL_OPT_RECONNECT选项来控制reconnect行为。
3） 可通过mysql_query()或mysql_real_query()发送SQL语句到server。mysql_query()需要string以null终止。若要发送二进制数据可使用mysql_real_query()。
4） 有两种方法处理结果集。一种方式是调用mysql_store_result()，获取所有的行数据（all the rows），需要内存大；第二种方式是调用mysql_use_result()初始化一行一行获取数据，此函数仅仅初始化获取空间，实际没有得到任何行数据。最后调用mysql_free_result()释放内存。
5） 两种方式都可通过mysql_fetch_row()提取行数据，通过mysql_fetch_lengths()获取每行数据的size；通过重复调用mysql_fetch_field()获取一行内的列数据，或者调用mysql_fetch_field_direct()通过field number直接获取列数据，或者调用mysql_fetch_fields()一次性获取所有列数据。
6） 通过调用mysql_errno()和mysql_error()获取错误码或错误信息。
7） The API makes it possible for clients to respond appropriately to statements (retrieving rows only as necessary) without knowing whether the statement is a SELECT. You can do this by calling mysql_store_result() after each mysql_query() (or mysql_real_query()). If the result set call succeeds, the statement was a SELECT and you can read the rows. If the result set call fails, call mysql_field_count() to determine whether a result was actually to be expected. If mysql_field_count() returns zero, the statement returned no data (indicating that it was an INSERT, UPDATE, DELETE, and so forth), and was not expected to return rows. If mysql_field_count() is nonzero, the statement should have returned rows, but did not. This indicates that the statement was a SELECT that failed. See the description for mysql_field_count() for an example of how this can be done.
3.3 C API使用说明
1． 返回值。函数通常返回一个指针或一个整数。 Unless specified otherwise, functions returning a pointer return a non-NULL value to indicate success or a NULL value to indicate an error, and functions returning an integer return zero to indicate success or nonzero to indicate an error. Note that “nonzero” means just that. Unless the function description says otherwise, do not test against a value other than zero.
2． 在失败的函数调用后紧跟mysql_errno()或mysql_error()可以返回错误code或错误信息。如调用mysql_real_connect()报错，mysql_error()打印如下信息：
Authentication plugin 'caching_sha2_password' cannot be loaded: 找不到指定的模块。
mysql server从5.7版本开始，默认采用caching_sha2_password验证方式。若要改为原来的mysql_native_password方式，需在mysql shell中执行如下命令：
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root_pw';
3.4 C API两套函数调用
Mysql提供两套C API，一种通用SQL语句查询，另一种为预处理调用（prepared statements）。
预处理调用将SQL语句强制一分为二，第一部分为相同的命令和结构部分，第二部分为SQL的参数部分（如查询条件等）。用于执行相同SQL语句（只是查询或处理条件不同），执行效率更高。参考：MySql 的预处理（简书），MySQL之Prepared Statements（博文）
MySQL服务器-客户端协议提供prepared statements（预处理）调用，使用由mysql_stmt_init()返回的MYSQL_STMT句柄。Prepared execution is an efficient way to execute a statement more than once. The statement is first parsed to prepare it for execution. Then it is executed one or more times at a later time, using the statement handler returned by the initialization function.
Prepared execution执行速度快，使用二进制传输效率高，但有局限，需要实际测试，可参考：C API Prepared Statements。
3.5 示例
#include <iostream> #include <windows.h> #include <stdio.h> #include <time.h> #include "mysql.h" #pragma comment(lib,"../lib/libmysql.lib") #define REPEAT_READ_MAX 5 // 重读数据次数 #define MYSQL_SERVER_IP_LOCAL "127.0.0.1" // mysql服务器地址 #define MYSQL_SERVER_IP MYSQL_SERVER_IP_LOCAL #define MYSQL_SERVER_PORT 3306 // mysql server port #define MYSQL_USER "root" // mysql user #define MYSQL_PASSWD "wang" // mysql password #define MYSQL_DB "fenghua" // mysql database using namespace std; char SQL_INSERT_T[] = "insert into tabletest (id, insert_time) values (%ld, \'%s\');"; char SQL_INSERT_TT[] = "insert into tabletest (insert_time) values (\'%s\');"; char SQL_SELECT_LAST_2_T[] = "select * from tabletest order by insert_time DESC limit 2"; char SQL_T[1024] = { '\0' }; char stime[32]; unsigned int ID_T = 0; int myInsertData(MYSQL* mysql, char* sql) { if (!mysql || !sql) { return 1; } int ret = 0; if ((ret = mysql_query(mysql, sql)) != 0) { // cout << "Insert error " << ret << endl; return 1; } return 0; } int mySelectData(MYSQL* mysql, char* sql, unsigned int num) { if (!mysql || !sql) { return 1; } // char rg = '\n'; // 行分割 // char cg = '\t'; // 字段分割 unsigned long long rowcount = 0; unsigned int fieldcount = 0, cur_row = 0; MYSQL_RES* result = NULL; MYSQL_FIELD* field = NULL; //字段 MYSQL_ROW row = NULL; //记录 if (mysql_query(mysql, sql) != 0) { cout << "Select query error!" << endl; return 1; } result = mysql_store_result(mysql); if (result == NULL) { cout << "Select result error!" << endl; return 1; } rowcount = mysql_num_rows(result); //获取记录数 fieldcount = mysql_num_fields(result); //获取字段数 if (num == 0) { num = (unsigned int)rowcount; } cout << "Now the count of records: " << rowcount << endl; while ((row = mysql_fetch_row(result)) && (cur_row++ < num)) { for (unsigned int i = 0; i < fieldcount; i++) { if (row[i]) { cout << row[i]; } cout << '\t'; } cout << endl; } mysql_free_result(result); return 0; } void getDatetime(char* str, unsigned int size) { time_t rawtime; struct tm timeinfo; time(&rawtime); localtime_s(&timeinfo, &rawtime); strftime(str, size, "%Y-%m-%d %H:%M:%S", &timeinfo); // cout << str << endl; } int main(int argc, char* argv[]) { int ii = 0; int repeat_count = 0; ERROR_CODE ecode; mysql_library_init(0, NULL, NULL); MYSQL mysql; while (1) { repeat_count = 0; mysql_init(&mysql); // 连接mysql服务器 while (1) { cout << "Set charset ......"; if (0 == mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, "utf8"))//设置字符集 { cout << "OK !" << endl; } else { cout << "Failed !" << endl; Sleep(10); continue; } char user[32] = "root"; char pw[32] = "wang"; char dbb[32] = "fenghua"; cout << "Connect to mysql server " << MYSQL_SERVER_IP << "......" << endl; if (!mysql_real_connect(&mysql, (char*)(MYSQL_SERVER_IP), (char *)(MYSQL_USER), (char*)(MYSQL_PASSWD), (char*)(MYSQL_DB), MYSQL_SERVER_PORT, NULL, 0))//连接数据库 { cout << "Can't connect mysql " << ii << endl; Sleep(1000); } else { cout << "Welcome to mysql !" << endl; break; } } while(repeat_count < REPEAT_READ_MAX){ { { cout << "Post data to mysql ......" << endl; getDatetime(stime, sizeof(stime)); // sprintf_s(SQL_T, SQL_INSERT_T, ID_T++, stime); // insert fields sprintf_s(SQL_T, SQL_INSERT_TT, stime); // insert fields cout << SQL_T << endl; if (myInsertData(&mysql, SQL_T)) { cout << "Insert error!\n" << endl; repeat_count++; Sleep(1000); continue; } else { repeat_count = 0; } sprintf_s(SQL_T, SQL_SELECT_LAST_2_T); if (mySelectData(&mysql, SQL_T, 0)) { cout << "\t Select error!" << endl; } } Sleep(5000); } } mysql_close(&mysql); } mysql_server_end(); mysql_library_end(); return 0; }
注：当table的id设置为auto_increment时，可不插入id，id会自增加。
if ((0 == mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, "utf8")) && \ (0 == mysql_options(&mysql, MYSQL_OPT_READ_TIMEOUT, &ttimeout)) && \ (0 == mysql_options(&mysql, MYSQL_OPT_WRITE_TIMEOUT, &ttimeout)))
上述代码用于设置读写超时。
4. C++ API
MySql Connector/C++8是一个用于连接MySQL服务器的C++应用程序。Connector/C++8可用于访问实现文档存储的 MySQL服务器，或者使用SQL查询以传统方式访问。它支持使用XDevAPI开发C ++应用程序，或使用XDevAPI for C开发纯C应用程序，同时Connector/C++8还支持与之前使用Connector/C++1.1中基于JDBC的旧API开发的C++应用程序一起编译及开发(即：向后兼容)。但是，Connector/C++8的首选开发环境是使用XDevAPI或XDevAPI for C。
注意：connecotr/C++8是基于X plugin的X protocol进行通信，所以在使用connector/C++8时先确认MySql服务器端以加载mysqlx插件，对于使用基于JDBC的旧API的Connector/C++应用程序，不需要也不支持X插件。 此外，X Protocol使用的默认端口为33060，如果是使用之前的JDBC4.0进行开发则使用3306端口，监听的IP默认为任意可用IP。
MySql Connector/C++8支持的应用程序编程接口：
l X DevAPI
l X DevAPI for C
l 基于JDBC 4.0的API
支持X DevAPI 和 X DevAPI for C 的 MySql服务器版本 为 Mysql5.7.12及更高版本。
在mysql客户端中输入，查看以加载的插件信息
mysql> show plugins;
如果没有mysqlx，则使用以下命令加载插件(mysqlx)
mysql> INSTALL PLUGIN mysqlx SONAME 'mysqlx.so';
5. golang接口
Golang中database/sql包提供了保证SQL或类SQL数据库的泛用接口，并不提供具体的数据库驱动。使用database/sql包必须注入（至少）一个数据库驱动。
mysql常用的数据库驱动是github.com/go-sql-driver/mysql。
6. 高可用
MySQL主从复制是什么？ MySQL主从复制
主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。
1）配置多数据源；
2）使用mysql的proxy中间件代理工具；
第一种方式中，数据库和Application是有一定侵入性的，即我们的数据库更换时，application中的配置文件是需要手动修改的。而第二种方式中，我们可选择mysql proxy固定连接一个数据库，即使数据库地址更换也无需更换项目中的数据库连接配置。
同样，在开始配置实现MySQL读写分离之前，我们会遇到一个选型问题，那就是在诸多的MySQL的proxy中间件工具中，如
mysql-proxy、atlas、cobar、mycat、tddl、tinnydbrouter和mysql router等，我们该如何取舍呢？所以在择工具实现前，我们先对以上的proxy中间件做一个简单的优劣介绍，以便我们根据不同的场景选择。参考：
1． MySQL C API Function Overview
2． https://www.runoob.com/mysql/mysql-tutorial.html runoob
3． c++读写MySQL 调用C API
4． Connector/C++ 8.0 legacy C++ API based on JDBC4
7． 【C++】VS2015/VS2017连接Mysql数据库教程
10. golang操作mysql使用总结 sql操作
