【Ubuntu】安装MySQL

环境是Ubuntu 18.04

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

搞完了之后就检查一下MySQL的socket是否正常

netstat -tap | grep mysql

如果确认没啥问题就尝试登陆

sudo mysql -uroot -p

MySQL的初始用户是root，初始密码没有设置，所以这条命令运行完之后会让你输入密码，直接回车就好了。

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> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

mysql> 

如果你想改一下密码，那需要用

mysql> update mysql.user set authentication_string=password('mynewpassword') where user='root';

我把密码改成了mynewpassword，重启MySQL生效

mysql> update mysql.user set authentication_string=password('mynewpassword') where user='root' ;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 1

mysql> 

接下来写一个简单的程序来访问数据库，实现和上面一样的show tables功能：

#include <mysql/mysql.h>
#include <stdio.h>
#include <stdlib.h>
int main() 
{
    MYSQL *conn;
    MYSQL_RES *res;
    MYSQL_ROW row;
    char server[] = "localhost";
    char user[] = "debian-sys-maint";
    char password[] = "k2nXElkMI4oyq3OZ";
    char database[] = "mysql";
    
    conn = mysql_init(NULL);
    
    if (!mysql_real_connect(conn, server,user, password, database, 0, NULL, 0)) 
    {
        fprintf(stderr, "%s\n", mysql_error(conn));
        exit(1);
    }
    
    if (mysql_query(conn, "show tables")) 
    {
        fprintf(stderr, "%s\n", mysql_error(conn));
        exit(1);
    }
    
    res = mysql_use_result(conn);
    
    printf("MySQL Tables in mysql database:\n");
    
    while ((row = mysql_fetch_row(res)) != NULL)
    {
        printf("%s \n", row[0]);
    }
    
    mysql_free_result(res);
    mysql_close(conn);
    
    printf("finish! \n");
    return 0;
}

这个user和password看起来有点怪，来源于/etc/mysql/debian.cnf，内容如下(需要用sudo)：

# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = k2nXElkMI4oyq3OZ
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = k2nXElkMI4oyq3OZ
socket   = /var/run/mysqld/mysqld.sock

编译运行此程序

g++ -Wall a.cpp -o jp -lmysqlclient
./jp

-lmysqlclient是必要的。

运行结果如下：

MySQL Tables in mysql database:
columns_priv 
db 
engine_cost 
event 
func 
general_log 
gtid_executed 
help_category 
help_keyword 
help_relation 
help_topic 
innodb_index_stats 
innodb_table_stats 
ndb_binlog_index 
plugin 
proc 
procs_priv 
proxies_priv 
server_cost 
servers 
slave_master_info 
slave_relay_log_info 
slave_worker_info 
slow_log 
tables_priv 
time_zone 
time_zone_leap_second 
time_zone_name 
time_zone_transition 
time_zone_transition_type 
user 
finish! 

#参考
[1] ubuntu下连接mysql出现Access denied for user ‘rose’@‘localhost’ (using password: NO)的解决方法
[2] Ubuntu 安装mysql和简单操作