MySQL安装，建立，导入本地Txt文件

• MySQL安装教程参照如下：

Windows下MySQL的安装及初始化设置 - 简书

• MySQL修改数据默认存储路径

Windows 配置文件路径：C:\ProgramData\MySQL\MySQL Server 8.2\my.ini

MySQL修改数据默认存储路径_show global variables like %datadir%-CSDN博客

• 建立数据库

C:\>mysql -u testsql -p

mysql> create database mysqldb;

mysql> use mysqldb;

• 建立数据表

mysql> create table mytest_table(

        -> path varchar(500),

        -> value varchar(100)

        -> );

• 导入本地Txt文件，需要使用参数--local-infile重新登录mysql

https://www.cnblogs.com/djh222/p/10758240.html

mysql> load data local infile 'tt001.txt'

        -> into table mytest_table

        -> fields terminated by ','

        -> ;

• 查看数据表

mysql> select * from mytest_table limit 1;

• 删除数据表

mysql> drop table mytest_table;

• 查看数据表

mysql> show tables;

• 删除数据库

mysql> drop database mysqldb;

• 查看数据库

mysql> show databases;

• 查询数据库用户

mysql> select user from mysql.user;

• 设置用户远程连接权限

mysql> grant all privileges on *.* to 'testsql'@'%' with grant option;
Query OK, 0 rows affected (3.17 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.35 sec)

mysql> show grants for 'testsql'@'%';

其中，新建用户，和授予权限需要分开执行，否则报错。参照如下：

mysql版本:'for the right syntax to use near 'identified by 'password' with grant option'_bruce1992的技术博客_51CTO博客

• Ruby连接Mysql选项

Mysql2::Client.new (
  :host,
  :username,
  :password,
  :port,
  :database,
  :socket = '/path/to/mysql.sock',
  :flags = REMEMBER_OPTIONS | LONG_PASSWORD | LONG_FLAG | TRANSACTIONS | PROTOCOL_41 | SECURE_CONNECTION | MULTI_STATEMENTS,
  :encoding = 'utf8',
  :read_timeout = seconds,
  :write_timeout = seconds,
  :connect_timeout = seconds,
  :reconnect = true/false,
  :local_infile = true/false,
  :secure_auth = true/false,
  :default_file = '/path/to/my.cfg',
  :default_group = 'my.cfg section',
  :init_command => sql
)

• Ruby远程连接数据库代码

在连接的过程中会报与网络连接有关的error，此时可先2机器网络直连，排除局域网的原因。

def sql_connection
  begin
    Mysql2::Client.new(:host => '172.21.252.177',
                       :port => '3306',
                       :username => 'mysql',
                       :password => '111111',
                       :database => 'mydata'
                      )
  rescue Exception => e
    raise '#{e.message}'
  end
end

• Ruby远程操作数据库示例

results = sql_connection.query("SELECT VERSION()")
results.each do |row|
  puts row
end

results = sql_connection.query("select * from mytest_table limit 1")
results.each do |row|
  puts row
end

results = sql_connection.query("select * from mytest_table where path='This is test string.'")
results.each do |row|
  puts row
end