MYSQL – Records And Memorys

August 26, 2021August 26, 2021

怎么用heidisql 写mysql 5.1 的存储过程

例子：

DELIMITER $$
create PROCEDURE loopTables111 ()
BEGIN
DECLARE done INT;
DECLARE TableName VARCHAR(17);
DECLARE TablesCursor CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE SUBSTRING_INDEX(TABLE_NAME,'_',-1) = '20210826';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
#DECLARE CONTINUE HANDLER FOR NOT FOUND SET Finished = TRUE;

OPEN TablesCursor;

MainLoop: LOOP
FETCH TablesCursor INTO TableName;


SELECT TableName;

END LOOP;

CLOSE TablesCursor;
END$$
DELIMITER ;

DELIMITER $$

create PROCEDURE loopTables111 ()

BEGIN

DECLARE done INT;

DECLARE TableName VARCHAR(17);

DECLARE TablesCursor CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE SUBSTRING_INDEX(TABLE_NAME,'_',-1) = '20210826';

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

#DECLARE CONTINUE HANDLER FOR NOT FOUND SET Finished = TRUE;

OPEN TablesCursor;

MainLoop: LOOP

FETCH TablesCursor INTO TableName;

SELECT TableName;

END LOOP;

CLOSE TablesCursor;

END$$

DELIMITER ;

August 6, 2019

Mysql 遇到过的问题

2019-08-06 centos 6 , 32bit

当尝试打开wordpress网站时，出

can't connect to local mysql server through socket '/var/lib/mysql/mysql.sock'

1	can't connect to local mysql server through socket '/var/lib/mysql/mysql.sock'

如果直接在putty mysql -u root -p 连，出

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

1	ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

这时查看 mysql status :

service mysqld status

1	service mysqld status

状态是： mysqld dead but subsys locked

Google到解决方法是： https://support.plesk.com/hc/en-us/articles/360005078273-Unable-to-start-MySQL-because-of-lack-of-RAM-mysqld-dead-but-subsys-locked-

a) 先把锁文件删除

rm -f /var/lock/subsys/mysqld

1	rm -f /var/lock/subsys/mysqld

b) free – m , 看内存可用，如果大于200M , 就不用做其他操作，否则杀占内存过多的进程

c) 重新启动 mysql : service mysqld start

July 23, 2019August 13, 2019

use mysql in centos machine by c++ ，实现定时建表，取数等功能

确定centos 版本，用命令

cat /etc/centos-release

1	cat /etc/centos-release

结果是

CentOS release 6.10 (Final)

1	CentOS release 6.10 (Final)

也就是说，是 centos6

看是32位还是 64位

uname -m

uname -m

结果为

i686

i686

所以是 32位的

看 mysql 版本

mysql --version

1	mysql --version

显示

mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (i386) using readline 5.1

1	mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (i386) using readline 5.1

所以，是 mysql 5.1.73

先写一个最简单的显示mysql版本的程序

#include <iostream>

#include <mysql.h>
#include <stdio.h>
#include <stdlib.h>


using namespace std;

MYSQL *connection, mysql;

MYSQL_RES *result;

MYSQL_ROW row;

int query_state;

int main() {

        printf("MySQL client version: %s\n", mysql_get_client_info());
        return 0;

}

#include <iostream>

#include <mysql.h>

#include <stdio.h>

#include <stdlib.h>

using namespace std;

MYSQL *connection, mysql;

MYSQL_RES *result;

MYSQL_ROW row;

int query_state;

int main() {

printf("MySQL client version: %s\n", mysql_get_client_info());

return 0;

}

如何编译

mysql 安装后，会自带1个命令，有2个参数一个是：

mysql_config --cflags

1	mysql_config --cflags

它的输出结果是：

-I/usr/include/mysql  -g -pipe -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m32 -fasynchronous-unwind-tables -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-strict-aliasing -fwrapv -fPIC   -DUNIV_LINUX -DUNIV_LINUX

1	-I/usr/include/mysql -g -pipe -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m32 -fasynchronous-unwind-tables -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-strict-aliasing -fwrapv -fPIC -DUNIV_LINUX -DUNIV_LINUX

第二个是

mysql_config --libs

1	mysql_config --libs

输出：

-rdynamic -L/usr/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm -lssl -lcrypto

1	-rdynamic -L/usr/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm -lssl -lcrypto

所以，刚才的程序就可以这样编译：

g++ -o main $(mysql_config --cflags) main.cpp $(mysql_config --libs)

1	g++ -o main $(mysql_config --cflags) main.cpp $(mysql_config --libs)

来源： https://www.codingfriends.com/index.php/2010/02/17/mysql-connection-example/comment-page-1/

然后再做一个显示数据库所有表的程序，来源同上

#include <mysql.h>
#include <stdio.h>
#include <stdlib.h>
 
// just going to input the general details and not the port numbers
struct connection_details
{
    char *server;
    char *user;
    char *password;
    char *database;
};
 
MYSQL* mysql_connection_setup(struct connection_details mysql_details)
{
     // first of all create a mysql instance and initialize the variables within
    MYSQL *connection = mysql_init(NULL);
 
    // connect to the database with the details attached.
    if (!mysql_real_connect(connection,mysql_details.server, mysql_details.user, mysql_details.password, mysql_details.database, 0, NULL, 0)) {
      printf("Conection error : %s\n", mysql_error(connection));
      exit(1);
    }
    return connection;
}
 
MYSQL_RES* mysql_perform_query(MYSQL *connection, char *sql_query)
{
   // send the query to the database
   if (mysql_query(connection, sql_query))
   {
      printf("MySQL query error : %s\n", mysql_error(connection));
      exit(1);
   }
 
   return mysql_use_result(connection);
}
 
int main()
{
  MYSQL *conn;		// the connection
  MYSQL_RES *res;	// the results
  MYSQL_ROW row;	// the results row (line by line)
 
  struct connection_details mysqlD;
  mysqlD.server = "localhost";  // where the mysql database is
  mysqlD.user = "mysqlusername";		// the root user of mysql	
  mysqlD.password = "mysqlpassword"; // the password of the root user in mysql
  mysqlD.database = "mysql";	// the databse to pick
 
  // connect to the mysql database
  conn = mysql_connection_setup(mysqlD);
 
  // assign the results return to the MYSQL_RES pointer
  res = mysql_perform_query(conn, "show tables");
 
  printf("MySQL Tables in mysql database:\n");
  while ((row = mysql_fetch_row(res)) !=NULL)
      printf("%s\n", row[0]);
 
  /* clean up the database result set */
  mysql_free_result(res);
  /* clean up the database link */
  mysql_close(conn);
 
  return 0;
}

#include <mysql.h>

#include <stdio.h>

#include <stdlib.h>

// just going to input the general details and not the port numbers

struct connection_details

{

char *server;

char *user;

char *password;

char *database;

};

MYSQL* mysql_connection_setup(struct connection_details mysql_details)

{

// first of all create a mysql instance and initialize the variables within

MYSQL *connection = mysql_init(NULL);

// connect to the database with the details attached.

if (!mysql_real_connect(connection,mysql_details.server, mysql_details.user, mysql_details.password, mysql_details.database, 0, NULL, 0)) {

printf("Conection error : %s\n", mysql_error(connection));

exit(1);

}

return connection;

}

MYSQL_RES* mysql_perform_query(MYSQL *connection, char *sql_query)

{

// send the query to the database

if (mysql_query(connection, sql_query))

{

printf("MySQL query error : %s\n", mysql_error(connection));

exit(1);

}

return mysql_use_result(connection);

}

int main()

{

MYSQL *conn; // the connection

MYSQL_RES *res; // the results

MYSQL_ROW row; // the results row (line by line)

struct connection_details mysqlD;

mysqlD.server = "localhost"; // where the mysql database is

mysqlD.user = "mysqlusername"; // the root user of mysql

mysqlD.password = "mysqlpassword"; // the password of the root user in mysql

mysqlD.database = "mysql"; // the databse to pick

// connect to the mysql database

conn = mysql_connection_setup(mysqlD);

// assign the results return to the MYSQL_RES pointer

res = mysql_perform_query(conn, "show tables");

printf("MySQL Tables in mysql database:\n");

while ((row = mysql_fetch_row(res)) !=NULL)

printf("%s\n", row[0]);

/* clean up the database result set */

mysql_free_result(res);

/* clean up the database link */

mysql_close(conn);

return 0;

}

编译方法与之前的一样，如果把这句

res = mysql_perform_query(conn, "show tables");

1	res = mysql_perform_query(conn, "show tables");

换为：

res = mysql_perform_query(conn, "create table rb1910_20190620 (happentime DATETIME,lastprice double,b1 double,biVol int ,s1 double, s1Vol int);");

1	res = mysql_perform_query(conn, "create table rb1910_20190620 (happentime DATETIME,lastprice double,b1 double,biVol int ,s1 double, s1Vol int);");

即可实现建表

以上只是 Mysql相关，如果与 ctp 合并，参见：

http://www.notesoflyang.com/?p=2152

编译方法将改为

g++ -g -o ConnAndShowTable $(mysql_config --cflags) ConnAndShowTable.cpp $(mysql_config --libs) /home/cplusExer/useMysql/thostmduserapi.so

1	g++ -g -o ConnAndShowTable $(mysql_config --cflags) ConnAndShowTable.cpp $(mysql_config --libs) /home/cplusExer/useMysql/thostmduserapi.so

以下的程序实现了当把日期作为参数传进去之后（20190805）

能自动建表并插数

#include "ThostFtdcMdApi.h"
#include <mysql.h>
#include <stdio.h>
#include <stdlib.h> 
#include <iostream>
#include <string.h>
#include <sstream>

char dateParam[30];   // global param of date


// just going to input the general details and not the port numbers
struct connection_details
{
    char *server;
    char *user;
    char *password;
    char *database;
};
 
MYSQL* mysql_connection_setup(struct connection_details mysql_details)
{
     // first of all create a mysql instance and initialize the variables within
    MYSQL *connection = mysql_init(NULL);
 
    // connect to the database with the details attached.
    if (!mysql_real_connect(connection,mysql_details.server, mysql_details.user, mysql_details.password, mysql_details.database, 0, NULL, 0)) {
      printf("Conection error : %s\n", mysql_error(connection));
      exit(1);
    }
    return connection;
}
 
MYSQL_RES* mysql_perform_query(MYSQL *connection, char *sql_query)
{
   // send the query to the database
   if (mysql_query(connection, sql_query))
   {
      printf("MySQL query error : %s\n", mysql_error(connection));
      exit(1);
   }
 
   return mysql_use_result(connection);
}


class CMduserHandler : public CThostFtdcMdSpi
{
private:
  CThostFtdcMdApi *m_mdApi;
  MYSQL *conn;		// the connection
  MYSQL_RES *res;	// the results
  MYSQL_ROW row;	// the results row (line by line)
 
  struct connection_details mysqlD;

public:
  CMduserHandler()
  {

	  struct connection_details mysqlD;
  	mysqlD.server = "localhost";  // where the mysql database is
  	mysqlD.user = "root";		// the root user of mysql	
 	 mysqlD.password = "Piercing77#"; // the password of the root user in mysql
 	 mysqlD.database = "shfuture";	// the databse to pick
 

	  // connect to the mysql database
  	conn = mysql_connection_setup(mysqlD);

  }

 ~CMduserHandler()
 {
   mysql_free_result(res);
   /* clean up the database link */
   mysql_close(conn);

  }
  
  void connect()
  {

//创建并初始化API

      m_mdApi = CThostFtdcMdApi::CreateFtdcMdApi("", true, true);

      m_mdApi->RegisterSpi(this);

     m_mdApi->RegisterFront("tcp://218.28.130.102:41413");    //real
     //m_mdApi->RegisterFront("tcp://210.5.151.249:51205");    // Pan Hou

      m_mdApi->Init();
  }


//登陆

  void login()

  {

      CThostFtdcReqUserLoginField t = {0};

      while (m_mdApi->ReqUserLogin(&t, 1)!=0) sleep(1);

  }


// 订阅行情

  void subscribe()

  {

      char **ppInstrument=new char * [50];

      ppInstrument[0] = "rb1910";
      //ppInstrument[1] = "jm1901";

	 // ppInstrument[1] = "IF1812";

	  ppInstrument[1] = "IF1909";

	  ppInstrument[2] = "IC1909";

	  ppInstrument[3] = "ni1909";

      while (m_mdApi->SubscribeMarketData(ppInstrument, 4)!=0) sleep(1);
  }


  //接收行情

  void OnRtnDepthMarketData(CThostFtdcDepthMarketDataField *pDepthMarketData)
  {
		  //printf("%d,%d,%d",(int)pDepthMarketData->LastPrice,(int)pDepthMarketData->BidPrice1,(int)pDepthMarketData->BidVolume1);
    //      printf("\n");
		  //printf("%d,%d",(int)pDepthMarketData->AskPrice1,(int)pDepthMarketData->AskVolume1);
    //      printf("\n");

		  //printf("%s %s",(char*)pDepthMarketData->TradingDay,(char*)pDepthMarketData->UpdateTime);



 // MYSQL *conn;		// the connection
 // MYSQL_RES *res;	// the results
 // MYSQL_ROW row;	// the results row (line by line)
 
  //struct connection_details mysqlD;
  //mysqlD.server = "localhost";  // where the mysql database is
  //mysqlD.user = "root";		// the root user of mysql	
  //mysqlD.password = "Piercing77#"; // the password of the root user in mysql
  //mysqlD.database = "shfuture";	// the databse to pick
 

  // connect to the mysql database
  //conn = mysql_connection_setup(mysqlD);
		

	     if((pDepthMarketData->BidVolume1>1000000)||(pDepthMarketData->BidVolume1<-1000000))
			 return;

	     if((pDepthMarketData->BidPrice1>1000000)||(pDepthMarketData->BidPrice1<-1000000))
			 return;

		  std::string str((char*)pDepthMarketData->ActionDay);
		  std::string strtime((char*)pDepthMarketData->UpdateTime);
		  std::string strDayTime = str.substr(0,4) + '-' + str.substr(4,2) + '-' +  str.substr(6,2) + ' ' + strtime ;


		  //combine update char*    example : "INSERT INTO rb1 VALUES('1950-11-12 00:00:00', 100,200,300,400)"
		  char updatestring[256];
		  memset(updatestring, '\0', sizeof(updatestring));


		  if(strcmp (pDepthMarketData->InstrumentID,"rb1910")==0)
		  {
		     strcpy(updatestring,"INSERT INTO rb1910_");
			 strncat(updatestring,dateParam, sizeof(updatestring)); 
			 strncat(updatestring,  " VALUES('", sizeof(updatestring)); 
			 
		  }
		  else if(strcmp (pDepthMarketData->InstrumentID,"IF1909")==0) 
		  {
			 strcpy(updatestring,"INSERT INTO if1909_");
			 strncat(updatestring,dateParam, sizeof(updatestring)); 
			 strncat(updatestring,  " VALUES('", sizeof(updatestring)); 

		  }
		  else if(strcmp (pDepthMarketData->InstrumentID,"IC1909")==0)
		  {
			 strcpy(updatestring,"INSERT INTO ic1909_");
			 strncat(updatestring,dateParam, sizeof(updatestring)); 
			 strncat(updatestring,  " VALUES('", sizeof(updatestring)); 

		  }
		  else if(strcmp (pDepthMarketData->InstrumentID,"ni1909")==0)
		  {
			 strcpy(updatestring,"INSERT INTO ni1909_");
			 strncat(updatestring,dateParam, sizeof(updatestring)); 
			 strncat(updatestring,  " VALUES('", sizeof(updatestring)); 

		  }

		  else
		  {
			  //strcpy(updatestring,"INSERT INTO if20181221 VALUES('");
		  }
		  
		  strncat(updatestring, strDayTime.c_str(), sizeof(updatestring));
		  strncat(updatestring, "',", sizeof(updatestring));


		  std::ostringstream ostr;
		  ostr << pDepthMarketData->LastPrice;
		  std::string sLastPrice = ostr.str();
		  strncat(updatestring, sLastPrice.c_str(), sizeof(updatestring));
		  
		  strncat(updatestring, ",", sizeof(updatestring));

		  std::ostringstream ostr1;
		  ostr1 << pDepthMarketData->BidPrice1;
		  std::string sBidPrice1 = ostr1.str();
		  strncat(updatestring, sBidPrice1.c_str(), sizeof(updatestring));	  
		  

		  strncat(updatestring, ",", sizeof(updatestring));

		  std::ostringstream ostr2;
		  ostr2 << (int)pDepthMarketData->BidVolume1;
		  std::string sBidVolume1 = ostr2.str();
		  strncat(updatestring, sBidVolume1.c_str(), sizeof(updatestring));	  


		  strncat(updatestring, ",", sizeof(updatestring));

		  std::ostringstream ostr3;
		  ostr3 << pDepthMarketData->AskPrice1;
		  std::string sAskPrice1 = ostr3.str();
		  strncat(updatestring, sAskPrice1.c_str(), sizeof(updatestring));	  


		  strncat(updatestring, ",", sizeof(updatestring));

		  std::ostringstream ostr4;
		  ostr4 << (int)pDepthMarketData->AskVolume1;
		  std::string sAskVolume1 = ostr4.str();
		  strncat(updatestring, sAskVolume1.c_str(), sizeof(updatestring));	  

		 		  
		  strncat(updatestring, ")", sizeof(updatestring));
		  
		  std::cout<<updatestring<<std::endl;

		  res = mysql_perform_query(conn,updatestring);
		  mysql_free_result(res);
		  /* clean up the database link */
 		 //mysql_close(conn);

		  
		 return;
  }

}; 
int main(int argc, char* argv[])
{
  MYSQL *conn;		// the connection
  MYSQL_RES *res;	// the results
  MYSQL_ROW row;	// the results row (line by line)
 
  struct connection_details mysqlD;
  mysqlD.server = "localhost";  // where the mysql database is
  mysqlD.user = "root";		// the root user of mysql	
  mysqlD.password = "Piercing77#"; // the password of the root user in mysql
  mysqlD.database = "shfuture";	// the databse to pick
 
   int i;
   printf("%d\n",argc);
   for(i=0;i<=argc-1;i++)
   {
        printf("%s\n",argv[i]);
   }

  memset(dateParam, '\0', sizeof(dateParam));
  strcpy(dateParam,argv[1]);


  // connect to the mysql database
  conn = mysql_connection_setup(mysqlD);
 
  // assign the results return to the MYSQL_RES pointer
 
  // res = mysql_perform_query(conn, "show tables");

 // res = mysql_perform_query(conn, "create table rb1910_20190620 (happentime DATETIME,lastprice double,b1 double,biVol int ,s1 double, s1Vol int);");

  char CreateTableString[256];
   memset(CreateTableString, '\0', sizeof(CreateTableString));

  strcpy(CreateTableString,"create table if not exists rb1910_");
  strncat(CreateTableString, argv[1], sizeof(CreateTableString)); 

  strncat(CreateTableString," (happentime DATETIME,lastprice double,b1 double,biVol int ,s1 double, s1Vol int);",sizeof(CreateTableString));


   printf("%s\n",CreateTableString);
  res = mysql_perform_query(conn, CreateTableString);


//
   memset(CreateTableString, '\0', sizeof(CreateTableString));

  strcpy(CreateTableString,"create table if not exists if1909_");
  strncat(CreateTableString, argv[1], sizeof(CreateTableString)); 

  strncat(CreateTableString," (happentime DATETIME,lastprice double,b1 double,biVol int ,s1 double, s1Vol int);",sizeof(CreateTableString));


   printf("%s\n",CreateTableString);
  res = mysql_perform_query(conn, CreateTableString);


   memset(CreateTableString, '\0', sizeof(CreateTableString));

  strcpy(CreateTableString,"create table if not exists ni1909_");
  strncat(CreateTableString, argv[1], sizeof(CreateTableString)); 

  strncat(CreateTableString," (happentime DATETIME,lastprice double,b1 double,biVol int ,s1 double, s1Vol int);",sizeof(CreateTableString));


   printf("%s\n",CreateTableString);
  res = mysql_perform_query(conn, CreateTableString);

//
   memset(CreateTableString, '\0', sizeof(CreateTableString));

  strcpy(CreateTableString,"create table if not exists ic1909_");
  strncat(CreateTableString, argv[1], sizeof(CreateTableString)); 

  strncat(CreateTableString," (happentime DATETIME,lastprice double,b1 double,biVol int ,s1 double, s1Vol int);",sizeof(CreateTableString));


   printf("%s\n",CreateTableString);
  res = mysql_perform_query(conn, CreateTableString);



 
 // printf("MySQL Tables in mysql database:\n");
// while ((row = mysql_fetch_row(res)) !=NULL)
	 //printf("%s\n", row[0]);
 
  /* clean up the database result set */
  mysql_free_result(res);
  /* clean up the database link */
  mysql_close(conn);
  
  
   CMduserHandler *mduser = new CMduserHandler;

  mduser->connect();

  mduser->login();

  mduser->subscribe();

 // while(1)
  //{}

  sleep(1000);
 

  return 0;
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

#include "ThostFtdcMdApi.h"

#include <mysql.h>

#include <stdio.h>

#include <stdlib.h>

#include <iostream>

#include <string.h>

#include <sstream>

char dateParam[30]; // global param of date

// just going to input the general details and not the port numbers

struct connection_details

{

char *server;

char *user;

char *password;

char *database;

};

MYSQL* mysql_connection_setup(struct connection_details mysql_details)

{

// first of all create a mysql instance and initialize the variables within

MYSQL *connection = mysql_init(NULL);

// connect to the database with the details attached.

if (!mysql_real_connect(connection,mysql_details.server, mysql_details.user, mysql_details.password, mysql_details.database, 0, NULL, 0)) {

printf("Conection error : %s\n", mysql_error(connection));

exit(1);

}

return connection;

}

MYSQL_RES* mysql_perform_query(MYSQL *connection, char *sql_query)

{

// send the query to the database

if (mysql_query(connection, sql_query))

{

printf("MySQL query error : %s\n", mysql_error(connection));

exit(1);

}

return mysql_use_result(connection);

}

class CMduserHandler : public CThostFtdcMdSpi

{

private:

CThostFtdcMdApi *m_mdApi;

MYSQL *conn; // the connection

MYSQL_RES *res; // the results

MYSQL_ROW row; // the results row (line by line)

struct connection_details mysqlD;

public:

CMduserHandler()

{

struct connection_details mysqlD;

mysqlD.server = "localhost"; // where the mysql database is

mysqlD.user = "root"; // the root user of mysql

mysqlD.password = "Piercing77#"; // the password of the root user in mysql

mysqlD.database = "shfuture"; // the databse to pick

// connect to the mysql database

conn = mysql_connection_setup(mysqlD);

}

~CMduserHandler()

{

mysql_free_result(res);

/* clean up the database link */

mysql_close(conn);

}

void connect()

{

//创建并初始化API

m_mdApi = CThostFtdcMdApi::CreateFtdcMdApi("", true, true);

m_mdApi->RegisterSpi(this);

m_mdApi->RegisterFront("tcp://218.28.130.102:41413"); //real

//m_mdApi->RegisterFront("tcp://210.5.151.249:51205"); // Pan Hou

m_mdApi->Init();

}

//登陆

void login()

{

CThostFtdcReqUserLoginField t = {0};

while (m_mdApi->ReqUserLogin(&t, 1)!=0) sleep(1);

}

// 订阅行情

void subscribe()

{

char **ppInstrument=new char * [50];

ppInstrument[0] = "rb1910";

//ppInstrument[1] = "jm1901";

// ppInstrument[1] = "IF1812";

ppInstrument[1] = "IF1909";

ppInstrument[2] = "IC1909";

ppInstrument[3] = "ni1909";

while (m_mdApi->SubscribeMarketData(ppInstrument, 4)!=0) sleep(1);

}

//接收行情

void OnRtnDepthMarketData(CThostFtdcDepthMarketDataField *pDepthMarketData)

{

//printf("%d,%d,%d",(int)pDepthMarketData->LastPrice,(int)pDepthMarketData->BidPrice1,(int)pDepthMarketData->BidVolume1);

// printf("\n");

//printf("%d,%d",(int)pDepthMarketData->AskPrice1,(int)pDepthMarketData->AskVolume1);

// printf("\n");

//printf("%s %s",(char*)pDepthMarketData->TradingDay,(char*)pDepthMarketData->UpdateTime);

// MYSQL *conn; // the connection

// MYSQL_RES *res; // the results

// MYSQL_ROW row; // the results row (line by line)

//struct connection_details mysqlD;

//mysqlD.server = "localhost"; // where the mysql database is

//mysqlD.user = "root"; // the root user of mysql

//mysqlD.password = "Piercing77#"; // the password of the root user in mysql

//mysqlD.database = "shfuture"; // the databse to pick

// connect to the mysql database

//conn = mysql_connection_setup(mysqlD);

if((pDepthMarketData->BidVolume1>1000000)||(pDepthMarketData->BidVolume1<-1000000))

return;

if((pDepthMarketData->BidPrice1>1000000)||(pDepthMarketData->BidPrice1<-1000000))

return;

std::string str((char*)pDepthMarketData->ActionDay);

std::string strtime((char*)pDepthMarketData->UpdateTime);

std::string strDayTime = str.substr(0,4) + '-' + str.substr(4,2) + '-' + str.substr(6,2) + ' ' + strtime ;

//combine update char* example : "INSERT INTO rb1 VALUES('1950-11-12 00:00:00', 100,200,300,400)"

char updatestring[256];

memset(updatestring, '\0', sizeof(updatestring));

if(strcmp (pDepthMarketData->InstrumentID,"rb1910")==0)

{

strcpy(updatestring,"INSERT INTO rb1910_");

strncat(updatestring,dateParam, sizeof(updatestring));

strncat(updatestring, " VALUES('", sizeof(updatestring));

}

else if(strcmp (pDepthMarketData->InstrumentID,"IF1909")==0)

{

strcpy(updatestring,"INSERT INTO if1909_");

strncat(updatestring,dateParam, sizeof(updatestring));

strncat(updatestring, " VALUES('", sizeof(updatestring));

}

else if(strcmp (pDepthMarketData->InstrumentID,"IC1909")==0)

{

strcpy(updatestring,"INSERT INTO ic1909_");

strncat(updatestring,dateParam, sizeof(updatestring));

strncat(updatestring, " VALUES('", sizeof(updatestring));

}

else if(strcmp (pDepthMarketData->InstrumentID,"ni1909")==0)

{

strcpy(updatestring,"INSERT INTO ni1909_");

strncat(updatestring,dateParam, sizeof(updatestring));

strncat(updatestring, " VALUES('", sizeof(updatestring));

}

else

{

//strcpy(updatestring,"INSERT INTO if20181221 VALUES('");

}

strncat(updatestring, strDayTime.c_str(), sizeof(updatestring));

strncat(updatestring, "',", sizeof(updatestring));

std::ostringstream ostr;

ostr << pDepthMarketData->LastPrice;

std::string sLastPrice = ostr.str();

strncat(updatestring, sLastPrice.c_str(), sizeof(updatestring));

strncat(updatestring, ",", sizeof(updatestring));

std::ostringstream ostr1;

ostr1 << pDepthMarketData->BidPrice1;

std::string sBidPrice1 = ostr1.str();

strncat(updatestring, sBidPrice1.c_str(), sizeof(updatestring));

strncat(updatestring, ",", sizeof(updatestring));

std::ostringstream ostr2;

ostr2 << (int)pDepthMarketData->BidVolume1;

std::string sBidVolume1 = ostr2.str();

strncat(updatestring, sBidVolume1.c_str(), sizeof(updatestring));

strncat(updatestring, ",", sizeof(updatestring));

std::ostringstream ostr3;

ostr3 << pDepthMarketData->AskPrice1;

std::string sAskPrice1 = ostr3.str();

strncat(updatestring, sAskPrice1.c_str(), sizeof(updatestring));

strncat(updatestring, ",", sizeof(updatestring));

std::ostringstream ostr4;

ostr4 << (int)pDepthMarketData->AskVolume1;

std::string sAskVolume1 = ostr4.str();

strncat(updatestring, sAskVolume1.c_str(), sizeof(updatestring));

strncat(updatestring, ")", sizeof(updatestring));

std::cout<<updatestring<<std::endl;

res = mysql_perform_query(conn,updatestring);

mysql_free_result(res);

/* clean up the database link */

//mysql_close(conn);

return;

}

};

int main(int argc, char* argv[])

{

MYSQL *conn; // the connection

MYSQL_RES *res; // the results

MYSQL_ROW row; // the results row (line by line)

struct connection_details mysqlD;

mysqlD.server = "localhost"; // where the mysql database is

mysqlD.user = "root"; // the root user of mysql

mysqlD.password = "Piercing77#"; // the password of the root user in mysql

mysqlD.database = "shfuture"; // the databse to pick

int i;

printf("%d\n",argc);

for(i=0;i<=argc-1;i++)

{

printf("%s\n",argv[i]);

}

memset(dateParam, '\0', sizeof(dateParam));

strcpy(dateParam,argv[1]);

// connect to the mysql database

conn = mysql_connection_setup(mysqlD);

// assign the results return to the MYSQL_RES pointer

// res = mysql_perform_query(conn, "show tables");

// res = mysql_perform_query(conn, "create table rb1910_20190620 (happentime DATETIME,lastprice double,b1 double,biVol int ,s1 double, s1Vol int);");

char CreateTableString[256];

memset(CreateTableString, '\0', sizeof(CreateTableString));

strcpy(CreateTableString,"create table if not exists rb1910_");

strncat(CreateTableString, argv[1], sizeof(CreateTableString));

strncat(CreateTableString," (happentime DATETIME,lastprice double,b1 double,biVol int ,s1 double, s1Vol int);",sizeof(CreateTableString));

printf("%s\n",CreateTableString);

res = mysql_perform_query(conn, CreateTableString);

memset(CreateTableString, '\0', sizeof(CreateTableString));

strcpy(CreateTableString,"create table if not exists if1909_");

strncat(CreateTableString, argv[1], sizeof(CreateTableString));

strncat(CreateTableString," (happentime DATETIME,lastprice double,b1 double,biVol int ,s1 double, s1Vol int);",sizeof(CreateTableString));

printf("%s\n",CreateTableString);

res = mysql_perform_query(conn, CreateTableString);

memset(CreateTableString, '\0', sizeof(CreateTableString));

strcpy(CreateTableString,"create table if not exists ni1909_");

strncat(CreateTableString, argv[1], sizeof(CreateTableString));

strncat(CreateTableString," (happentime DATETIME,lastprice double,b1 double,biVol int ,s1 double, s1Vol int);",sizeof(CreateTableString));

printf("%s\n",CreateTableString);

res = mysql_perform_query(conn, CreateTableString);

memset(CreateTableString, '\0', sizeof(CreateTableString));

strcpy(CreateTableString,"create table if not exists ic1909_");

strncat(CreateTableString, argv[1], sizeof(CreateTableString));

strncat(CreateTableString," (happentime DATETIME,lastprice double,b1 double,biVol int ,s1 double, s1Vol int);",sizeof(CreateTableString));

printf("%s\n",CreateTableString);

res = mysql_perform_query(conn, CreateTableString);

// printf("MySQL Tables in mysql database:\n");

// while ((row = mysql_fetch_row(res)) !=NULL)

//printf("%s\n", row[0]);

/* clean up the database result set */

mysql_free_result(res);

/* clean up the database link */

mysql_close(conn);

CMduserHandler *mduser = new CMduserHandler;

mduser->connect();

mduser->login();

mduser->subscribe();

// while(1)

//{}

sleep(1000);

return 0;

}

centos 7 用 sleep 函数加 #include <unistd.h>

此程序的问题是 cpu 占用过高，在centos 执行：

ps -aux

ps -aux

发现占用率是百分100 ，

因为原来的写法是：

while(1) {}

改为 sleep(大整数)即可

看进程的方法来自：

https://www.cyberciti.biz/faq/how-to-check-running-process-in-linux-using-command-line/

此外，还存在的问题是内存泄漏，随着程序的运行，内存会很快耗尽 (经过观察，不存在这个问题，是程序一开始执行内存占用急增，过一阵就会恢复正常 )

检查内存泄漏的工具是 valgrind

安装方法：

wget https://sourceware.org/pub/valgrind/valgrind-3.15.0.tar.bz2  //download valgrind
tar xvjf valgrind-3.15.0.tar.bz2                            //now extract
cd valgrind-3.15.0                                          //move to the corresponding dir
./configure                                                //prepare everything
make
make install                                               //now install

wget https://sourceware.org/pub/valgrind/valgrind-3.15.0.tar.bz2 //download valgrind

tar xvjf valgrind-3.15.0.tar.bz2 //now extract

cd valgrind-3.15.0 //move to the corresponding dir

./configure //prepare everything

make

make install //now install

参拷： https://stackoverflow.com/questions/22495744/how-to-install-valgrind-3-9-0-in-centos-5-5

安装后：

valgrind --tool=memcheck --leak-check=yes ./ConnAndShowTable 20190806

1	valgrind --tool=memcheck --leak-check=yes ./ConnAndShowTable 20190806

执行

发现程序执行一段时间后会自己结束

是因为sleep过短的原因，改为 sleep(26000) , 约7个小时即可

如何定时执行此程序并定时关闭

~~a) 因为程序运行在美国虚拟机，首先要知道当前的中国时间~~

<del>/* gmtime example */
#include <stdio.h>      /* puts, printf */
#include <time.h>       /* time_t, struct tm, time, gmtime */

#define MST (-7)
#define UTC (0)
#define CCT (+8)

int main ()
{
  time_t rawtime;
  struct tm * ptm;

  time ( &rawtime );

  ptm = gmtime ( &rawtime );

  puts ("Current time around the World:");
  printf ("Phoenix, AZ (U.S.) :  %2d:%02d\n", (ptm->tm_hour+MST)%24, ptm->tm_min);
  printf ("Reykjavik (Iceland) : %2d:%02d\n", (ptm->tm_hour+UTC)%24, ptm->tm_min);
  printf ("Beijing (China) :     %2d:%02d\n", (ptm->tm_hour+CCT)%24, ptm->tm_min);

  return 0;
}</del>

<del>/* gmtime example */

#include <stdio.h> /* puts, printf */

#include <time.h> /* time_t, struct tm, time, gmtime */

#define MST (-7)

#define UTC (0)

#define CCT (+8)

int main ()

{

time_t rawtime;

struct tm * ptm;

time ( &rawtime );

ptm = gmtime ( &rawtime );

puts ("Current time around the World:");

printf ("Phoenix, AZ (U.S.) : %2d:%02d\n", (ptm->tm_hour+MST)%24, ptm->tm_min);

printf ("Reykjavik (Iceland) : %2d:%02d\n", (ptm->tm_hour+UTC)%24, ptm->tm_min);

printf ("Beijing (China) : %2d:%02d\n", (ptm->tm_hour+CCT)%24, ptm->tm_min);

return 0;

}</del>

~~来源 : http://www.cplusplus.com/reference/ctime/gmtime/~~

~~编译方法：~~

<del>g++ -o showChinaTime showChinaTime.cpp</del>

1	<del>g++ -o showChinaTime showChinaTime.cpp</del>

b) 要有一个能实现cron trigger 的库，要能在 centos 运行的非.net版本

一开始想用这个： https://stackoverflow.com/questions/3618499/c-c-api-to-decode-cron-style-timings

是c++程序可以调用的lib ，但是下载已不可用，是 2013年的

或者可以用centos系统自带的crontab , 定时执行 c++ 程序

https://blog.csdn.net/capecape/article/details/78515558

思路是：先写一个脚本判断时间，该执行还是该关闭程序

然后用 crontab 每小时执行脚本

如何在 bash 脚本判断时间： https://unix.stackexchange.com/questions/395933/how-to-check-if-the-current-time-is-between-2300-and-0630

自动启动的脚本是这样的：

#!/bin/bash

#printf "Memory information\n"
#free -m
#printf "Disk information\n"
#df -h

echo "Today is $(date)"
echo "In UTC format Today is $(date -u)"
printf "Now will judge time\n"


#change date from edt to china date
dateStr=""
case $(date -d " + 12 hour" +'%H:%M') in    # us east time add 12 hour is china time
    (8:*)        echo morningOpen;;
    (15:*)       echo morningClose;;
    (20:*)        echo nightOpen;
                  echo doOpen;;
    (17:*)        echo nightClose;
                 dateStr=$(date -d " + 24 hour"  '+%Y%m%d');
                echo $dateStr;
                #exec /home/cplusExer/useMysql/ConnAndShowTable  $dateStr;;
                nohup /home/cplusExer/useMysql/ConnAndShowTable  $dateStr > /dev/null &;;
esac

#!/bin/bash

#printf "Memory information\n"

#free -m

#printf "Disk information\n"

#df -h

echo "Today is $(date)"

echo "In UTC format Today is $(date -u)"

printf "Now will judge time\n"

#change date from edt to china date

dateStr=""

case $(date -d " + 12 hour" +'%H:%M') in # us east time add 12 hour is china time

(8:*) echo morningOpen;;

(15:*) echo morningClose;;

(20:*) echo nightOpen;

echo doOpen;;

(17:*) echo nightClose;

dateStr=$(date -d " + 24 hour" '+%Y%m%d');

echo $dateStr;

#exec /home/cplusExer/useMysql/ConnAndShowTable $dateStr;;

nohup /home/cplusExer/useMysql/ConnAndShowTable $dateStr > /dev/null &;;

esac

需要注意的是这句： dateStr=$(date -d ” + 24 hour” ‘+%Y%m%d’);

等号2测不能有空格，来源：

https://stackoverflow.com/questions/9556517/variable-assignment-inside-a-case-statement-bash

此外，用 nohup 来后台执行

让脚本可执行的方法是：

chmod +x bashTest02.sh

1	chmod +x bashTest02.sh

如何自动停止

#!/bin/bash

#echo "Today is $(date)"

#pidof ConnAndShowTable>/dev/null && echo "Service is running" || echo "Service NOT running"

pid=0
pid=$(pidof ConnAndShowTable)
#echo $pid
if [ -z "$pid" ]
then
      echo "\$var is empty"
      echo $pid
else
      kill -9 $pid
fi

#!/bin/bash

#echo "Today is $(date)"

#pidof ConnAndShowTable>/dev/null && echo "Service is running" || echo "Service NOT running"

pid=0

pid=$(pidof ConnAndShowTable)

#echo $pid

if [ -z "$pid" ]

then

echo "\$var is empty"

echo $pid

else

kill -9 $pid

所以，最终的脚本是这样的：

#!/bin/bash

#printf "Memory information\n"
#free -m
#printf "Disk information\n"
#df -h

echo "Today is $(date)"
echo "In UTC format Today is $(date -u)"
printf "Now will judge time\n"
#echo $(date -d " + 12 hour" +'%H:%M')

#change date from edt to china date
dateStr=""
case $(date -d " + 12 hour" +'%H:%M') in    # us east time add 12 hour is china time
    (02:*)        echo nightClose;
                 pid=0;
                 pid=$(pidof ConnAndShowTable);
                 if [[ -z "$pid" ]];
                 then
                    echo $pid;
                 else
                    kill -9 $pid;
                 fi;;
    (16:*)       echo morningClose;;
    (20:*)       echo nightOpen;
                 dateStr=$(date -d " + 24 hour"  '+%Y%m%d');
                 nohup /home/cplusExer/useMysql/ConnAndShowTable  $dateStr > /dev/null &;;
    (08:*)        echo morningOpen;
                 dateStr=$(date -d " + 24 hour"  '+%Y%m%d');
                echo $dateStr;
                #exec /home/cplusExer/useMysql/ConnAndShowTable  $dateStr;;
                nohup /home/cplusExer/useMysql/ConnAndShowTable  $dateStr > /dev/null &;;
esac

#!/bin/bash

#printf "Memory information\n"

#free -m

#printf "Disk information\n"

#df -h

echo "Today is $(date)"

echo "In UTC format Today is $(date -u)"

printf "Now will judge time\n"

#echo $(date -d " + 12 hour" +'%H:%M')

#change date from edt to china date

dateStr=""

case $(date -d " + 12 hour" +'%H:%M') in # us east time add 12 hour is china time

(02:*) echo nightClose;

pid=0;

pid=$(pidof ConnAndShowTable);

if [[ -z "$pid" ]];

then

echo $pid;

else

kill -9 $pid;

fi;;

(16:*) echo morningClose;;

(20:*) echo nightOpen;

dateStr=$(date -d " + 24 hour" '+%Y%m%d');

nohup /home/cplusExer/useMysql/ConnAndShowTable $dateStr > /dev/null &;;

(08:*) echo morningOpen;

dateStr=$(date -d " + 24 hour" '+%Y%m%d');

echo $dateStr;

#exec /home/cplusExer/useMysql/ConnAndShowTable $dateStr;;

nohup /home/cplusExer/useMysql/ConnAndShowTable $dateStr > /dev/null &;;

esac

centos crontab

如何查看现有定时任务，新建任务，见：

https://tecadmin.net/crontab-in-linux-with-20-examples-of-cron-schedule/

我加了一个这样的任务：

* * * * *  /home/cplusExer/useMysql/bashScripts/bashTest02.sh

1	* * * * * /home/cplusExer/useMysql/bashScripts/bashTest02.sh

每分钟执行此脚本，但是，执行没有，或者执行的结果在哪里看？

答案是 ; https://stackoverflow.com/questions/28856326/crontab-simple-echo-not-running

如果要每小时，就

50 * * * *  /home/cplusExer/useMysql/bashScripts/bashTest.sh

1	50 * * * * /home/cplusExer/useMysql/bashScripts/bashTest.sh

调试用到的命令行

./ConnAndShowTable "20190802"

1	./ConnAndShowTable "20190802"

g++ -o ConnAndShowTable $(mysql_config --cflags) ConnAndShowTable.cpp $(mysql_config --libs)

1	g++ -o ConnAndShowTable $(mysql_config --cflags) ConnAndShowTable.cpp $(mysql_config --libs)

date -d " + 24 hour"  '+%Y%m%d'

1	date -d " + 24 hour" '+%Y%m%d'

sudo nano ConnAndShowTable.cpp

1	sudo nano ConnAndShowTable.cpp

待解决的问题：

a) 找一个读 config 文件的库

b) 自动建表时避开周末及休市日，这个要用到config

参考文档

https://sandeepghai.wordpress.com/2011/08/07/linking-of-mysql-database-with-c-on-linux-machine/

Linking of MYSQL Database with C++ on Linux Machine

https://stackoverflow.com/questions/8896963/connecting-to-mysql-through-c

关于 how to link

http://zetcode.com/db/mysqlc/

代码编译不通过，原因未知

May 22, 2019May 23, 2019

MYSQL store routine examples

传3个参数进去，表名 + 2个时间

BEGIN

SET @sql_text1 = concat('SELECT * FROM ',@tablename , ' where time(happentime)>''', @starttime , ''' and time(happentime)<''', @endtime,'''');

select @sql_text1;

PREPARE stmt FROM @sql_text1;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


END

BEGIN

SET @sql_text1 = concat('SELECT * FROM ',@tablename , ' where time(happentime)>''', @starttime , ''' and time(happentime)<''', @endtime,'''');

select @sql_text1;

PREPARE stmt FROM @sql_text1;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

END

调用方法：

call test_proc('if1906_20190521','09:30:00','10:00:00');

1	call test_proc('if1906_20190521','09:30:00','10:00:00');

在 while 循环把 start time 每次加5分钟

CREATE DEFINER=`root`@`localhost` PROCEDURE `test_proc`(
	IN `tablename` VARCHAR(50),
	IN `startTime` TIME,
	IN `endTime` TIME

)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN

WHILE endTime>ADDTIME(startTime, '00:05:00') DO 

	set startTime = ADDTIME(startTime,'00:05:00');
	
	select startTime;
END WHILE;


END

CREATE DEFINER=`root`@`localhost` PROCEDURE `test_proc`(

IN `tablename` VARCHAR(50),

IN `startTime` TIME,

IN `endTime` TIME

)

LANGUAGE SQL

NOT DETERMINISTIC

CONTAINS SQL

SQL SECURITY DEFINER

COMMENT ''

BEGIN

WHILE endTime>ADDTIME(startTime, '00:05:00') DO

set startTime = ADDTIME(startTime,'00:05:00');

select startTime;

END WHILE;

END

测试用sql 语句

select lastprice ,case when hour(happentime)<=11 then DATE_ADD(happentime,interval 90 minute) else happentime end  from if1906_20190521 where time(happentime)>'09:30:00' and time(happentime)<'10:00:00'

select lastprice,happentime from if1906_20190521 order by happentime limit 600

select min(lastprice),happentime  from if1906_20190521 where time(happentime)>'09:30:00' and time(happentime)<'09:35:00'

select max(lastprice),happentime  from if1906_20190521 where time(happentime)>'09:30:00' and time(happentime)<'09:35:00'
-----

select lastprice,happentime  from if1906_20190521 where lastprice = (select min(lastprice)  from if1906_20190521 where time(happentime)>'09:30:00' and time(happentime)<'09:35:00') and time(happentime)>'09:30:00' and time(happentime)<'09:35:00'
select lastprice,happentime  from if1906_20190521 where lastprice = (select max(lastprice)  from if1906_20190521 where time(happentime)>'09:30:00' and time(happentime)<'09:35:00') and time(happentime)>'09:30:00' and time(happentime)<'09:35:00'


select lastprice,happentime  from if1906_20190521 where lastprice = (select min(lastprice)  from if1906_20190521 where time(happentime)>'09:35:00' and time(happentime)<'09:40:00') and time(happentime)>'09:35:00' and time(happentime)<'09:40:00'
select lastprice,happentime  from if1906_20190521 where lastprice = (select max(lastprice)  from if1906_20190521 where time(happentime)>'09:35:00' and time(happentime)<'09:40:00') and time(happentime)>ADDTIMEADDTIME


call test_proc('if1906_20190521','09:30:00','09:46:00');


set @tablename = 'if1906_20190522';
set @starttime = '09:35:00';
set @endtime = '09:40:00';
SET @sql_text1 = concat('SELECT * FROM ',@tablename , ' where time(happentime)>"', @starttime , '" and time(happentime)<"', @endtime,'"');
select @sql_text1



set @tablename = 'if1906_20190522';
select @tablename


DECLARE total_count INT ;
SET total_count = 10;
select total_count;



set @"a"=now();
select @"a"

SELECT 'Comment';

select lastprice ,case when hour(happentime)<=11 then DATE_ADD(happentime,interval 90 minute) else happentime end from if1906_20190521 where time(happentime)>'09:30:00' and time(happentime)<'10:00:00'

select lastprice,happentime from if1906_20190521 order by happentime limit 600

select min(lastprice),happentime from if1906_20190521 where time(happentime)>'09:30:00' and time(happentime)<'09:35:00'

select max(lastprice),happentime from if1906_20190521 where time(happentime)>'09:30:00' and time(happentime)<'09:35:00'

-----

select lastprice,happentime from if1906_20190521 where lastprice = (select min(lastprice) from if1906_20190521 where time(happentime)>'09:30:00' and time(happentime)<'09:35:00') and time(happentime)>'09:30:00' and time(happentime)<'09:35:00'

select lastprice,happentime from if1906_20190521 where lastprice = (select max(lastprice) from if1906_20190521 where time(happentime)>'09:30:00' and time(happentime)<'09:35:00') and time(happentime)>'09:30:00' and time(happentime)<'09:35:00'

select lastprice,happentime from if1906_20190521 where lastprice = (select min(lastprice) from if1906_20190521 where time(happentime)>'09:35:00' and time(happentime)<'09:40:00') and time(happentime)>'09:35:00' and time(happentime)<'09:40:00'

select lastprice,happentime from if1906_20190521 where lastprice = (select max(lastprice) from if1906_20190521 where time(happentime)>'09:35:00' and time(happentime)<'09:40:00') and time(happentime)>ADDTIMEADDTIME

call test_proc('if1906_20190521','09:30:00','09:46:00');

set @tablename = 'if1906_20190522';

set @starttime = '09:35:00';

set @endtime = '09:40:00';

SET @sql_text1 = concat('SELECT * FROM ',@tablename , ' where time(happentime)>"', @starttime , '" and time(happentime)<"', @endtime,'"');

select @sql_text1

set @tablename = 'if1906_20190522';

select @tablename

DECLARE total_count INT ;

SET total_count = 10;

select total_count;

set @"a"=now();

select @"a"

SELECT 'Comment';

December 28, 2018September 27, 2020

How to backup and restore wordpress site

目标：备份远程 bandwagong的 wordpress到本地 centos 机器，包括articl , Pic , theme …

First backup 远程 mysql database

mysqldump --user=root --password=XXXXXX --opt wordpress > wordpressBK.sql

1	mysqldump --user=root --password=XXXXXX --opt wordpress > wordpressBK.sql

然后，restore 到本地

mysql --user=root --password=Piercing77# wordpress < /home/liuyang/wordpressBK20190812.sql

1	mysql --user=root --password=Piercing77# wordpress < /home/liuyang/wordpressBK20190812.sql

Tools —-> export —-> all contents

and download the generated file

在本地 centos 7 , 先翻墙 , 再下载 wordpress最新版本, 翻墙是为了能下载

cd /tmp

cd /tmp

wget http://wordpress.org/latest.tar.gz

1	wget http://wordpress.org/latest.tar.gz

解压到 /var/www/html/

sudo tar -xvzf latest.tar.gz -C /var/www/html

1	sudo tar -xvzf latest.tar.gz -C /var/www/html

在 mysql 建表

mysql -u root -p

1	mysql -u root -p

Piercing77#

CREATE USER wordpress@localhost IDENTIFIED BY "WordPressPWD77#";

1	CREATE USER wordpress@localhost IDENTIFIED BY "WordPressPWD77#";

## Create New Database ##
create database wordpress;

## Grant Privileges to Database ##
GRANT ALL ON wordpress.* TO wordpress@localhost;

## FLUSH privileges ##
FLUSH PRIVILEGES;

## Exit ##
exit

## Create New Database ##

create database wordpress;

## Grant Privileges to Database ##

GRANT ALL ON wordpress.* TO wordpress@localhost;

## FLUSH privileges ##

FLUSH PRIVILEGES;

## Exit ##

exit

注：如果不是第一次见表，可直接执行：

mysql --user=wordpress --password=WordPressPWD77# wordpress < /home/mysqlDB/wordpressBK20200927.sql

1	mysql --user=wordpress --password=WordPressPWD77# wordpress < /home/mysqlDB/wordpressBK20200927.sql

修改

/etc/httpd/conf/httpd.conf

1

/etc/httpd/conf/httpd.conf

在文件最后加上

<VirtualHost *:80>
    ServerAdmin webmaster@dummy-host.example.com
    DocumentRoot /var/www/html/wordpress
    ServerName wordpress
    ErrorLog /var/log/httpd/wordpress-error-log
    CustomLog /var/log/httpd/wordpress-acces-log common
</VirtualHost>

ServerAdmin webmaster@dummy-host.example.com

DocumentRoot /var/www/html/wordpress

ServerName wordpress

ErrorLog /var/log/httpd/wordpress-error-log

CustomLog /var/log/httpd/wordpress-acces-log common

</VirtualHost>

重启 apache

systemctl restart httpd.service

1	systemctl restart httpd.service

修改 /etc/hosts

加上

127.0.0.1  wordpress
192.168.3.116 wordpress

1 2	127.0.0.1 wordpress 192.168.3.116 wordpress

这时，在本机应该就能访问了，如果想让同一局域网的其他PC能通过

http://192.168.3.116 的形式访问

需要

sudo firewall-cmd --add-service=http

1	sudo firewall-cmd --add-service=http

learn from https://www.centos.org/forums/viewtopic.php?t=59161

但是，这样图片，视频等仍然存储在远程

是因为在 wp_posts表中， URL 仍然是远程的，需要手动改过来

参见： https://wpbeaches.com/updating-wordpress-mysql-database-after-moving-to-a-new-url/

主要是把 http://www.notesoflyang.com 改为 http://192.168.3.116 (公司局域网 IP )

一共4句 sql

UPDATE wp_options SET option_value = replace(option_value, 'http://www.notesoflyang.com', 'http://192.168.3.116') WHERE option_name = 'home' OR option_name = 'siteurl';

UPDATE wp_posts SET guid = replace(guid, 'http://www.notesoflyang.com','http://192.168.3.116');

UPDATE wp_posts SET post_content = replace(post_content, 'http://www.notesoflyang.com', 'http://192.168.3.116');

UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://www.notesoflyang.com','http://192.168.3.116');

UPDATE wp_options SET option_value = replace(option_value, 'http://www.notesoflyang.com', 'http://192.168.3.116') WHERE option_name = 'home' OR option_name = 'siteurl';

UPDATE wp_posts SET guid = replace(guid, 'http://www.notesoflyang.com','http://192.168.3.116');

UPDATE wp_posts SET post_content = replace(post_content, 'http://www.notesoflyang.com', 'http://192.168.3.116');

UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://www.notesoflyang.com','http://192.168.3.116');

此外，因为远程wordpress最开始是没有域名的，所以有一部分upload image 是以远程IP而不是域名做地址的，所有，还需要再执行一遍这4句，但是把域名改ip

UPDATE wp_options SET option_value = replace(option_value, 'http://176.122.178.37', 'http://192.168.3.116') WHERE option_name = 'home' OR option_name = 'siteurl';

UPDATE wp_posts SET guid = replace(guid, 'http://176.122.178.37','http://192.168.3.116');

UPDATE wp_posts SET post_content = replace(post_content, 'http://176.122.178.37', 'http://192.168.3.116');

UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://176.122.178.37','http://192.168.3.116');

UPDATE wp_options SET option_value = replace(option_value, 'http://176.122.178.37', 'http://192.168.3.116') WHERE option_name = 'home' OR option_name = 'siteurl';

UPDATE wp_posts SET guid = replace(guid, 'http://176.122.178.37','http://192.168.3.116');

UPDATE wp_posts SET post_content = replace(post_content, 'http://176.122.178.37', 'http://192.168.3.116');

UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://176.122.178.37','http://192.168.3.116');

最后，把 /var/www/html/wordpress/wp-content/uploads 文件夹的内容从远程copy到本地即可
finish backup

reference article :

how to backup wordpress without any plugin :

https://skillcrush.com/2015/04/23/backing-up-wordpress/

如果

systemctl start mysqld

1	systemctl start mysqld

失败，解决方法是

sudo systemctl start mysqld 这种是用 systemctl 管理程序启动，
很多教程这样写： /etc/init.d/mysqld start
但是在我的 /etc/init.d 并没有发现mysql

那就找 find / -name mysqld
发现在 /usr/sbin/mysqld
执行 /usr/sbin/mysqld start

如果登陆mysql 时出现：

Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13)

1	Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13)

注意最后的数字13

解决方法是：

sudo chown -R mysql:root /var/lib/mysql

1	sudo chown -R mysql:root /var/lib/mysql

可能是因为原来的mysql:mysql 没有这个目录的权限

方法来自：

https://serverfault.com/questions/497194/mysql-bind-on-unix-socket-permission-denied

December 24, 2018August 5, 2019

how to use gdb on centos machine

以这个程序为例

#include <iostream>
#include <mysql/mysql.h>
#include <stdio.h>
using namespace std;
#define SERVER "localhost"
#define USER "root"
#define PASSWORD "Piercing77#"
#define DATABASE "shfuture"

int main()
{
MYSQL *connect;
connect=mysql_init(NULL);
if (!connect)
{
cout<<"MySQL Initialization failed";
return 1;
}
connect=mysql_real_connect(connect, HOST, USER, PASSWORD ,
DATABASE ,0,NULL,0);
if (connect)
{
cout<<"connection Succeeded\n";
}
else
{
cout<<"connection failed\n";
}

#include <iostream>

#include <mysql/mysql.h>

#include <stdio.h>

using namespace std;

#define SERVER "localhost"

#define USER "root"

#define PASSWORD "Piercing77#"

#define DATABASE "shfuture"

int main()

{

MYSQL *connect;

connect=mysql_init(NULL);

if (!connect)

{

cout<<"MySQL Initialization failed";

return 1;

}

connect=mysql_real_connect(connect, HOST, USER, PASSWORD ,

DATABASE ,0,NULL,0);

if (connect)

{

cout<<"connection Succeeded\n";

}

else

{

cout<<"connection failed\n";

}

编译，注意要带 -g , 因为是c++ ，所以用 g++, c程序用 gcc

g++ -g  -o connAndQuery  connAndQuery.c $(mysql_config --libs)

1	g++ -g -o connAndQuery connAndQuery.c $(mysql_config --libs)

启动gdb

gdb ./connAndQuery

1	gdb ./connAndQuery

流程控制

break line number : 在某行下断点 ,centos 显示行号 ctrl + c

run 执行

next 执行一步

c : 执行到完

break

gdb 带参数执行：

gdb --args executablename arg1 arg2 arg3

1	gdb --args executablename arg1 arg2 arg3

来源： https://stackoverflow.com/questions/6121094/how-do-i-run-a-program-with-commandline-arguments-using-gdb-within-a-bash-script

参考文章

http://www.math.bas.bg/~nkirov/2005/netb151/debugging-with-gdb.html

http://kirste.userpage.fu-berlin.de/chemnet/use/info/gdb/gdb_6.html

November 26, 2018July 7, 2021

MYSQL commands

create DB

CREATE DATABASE SHFuture;

1	CREATE DATABASE SHFuture;

use DB

USE SHFuture;

1	USE SHFuture;

create table

create table rb (happentime DATE,b1 int,biVol int ,s1 int, s1Vol int);

1	create table rb (happentime DATE,b1 int,biVol int ,s1 int, s1Vol int);

create table rb3 (happentime DATETIME,lastprice int,b1 int,biVol int ,s1 int, s1Vol int);

1	create table rb3 (happentime DATETIME,lastprice int,b1 int,biVol int ,s1 int, s1Vol int);

create table rb3 (happentime DATETIME,lastprice double,b1 double,biVol int ,s1 double, s1Vol int);

1	create table rb3 (happentime DATETIME,lastprice double,b1 double,biVol int ,s1 double, s1Vol int);

create table everydayRecords(tablename char(30),high float, low float ,percent float, description varchar(200), ytdUSHigh float , ytdUSLow float , ytdUSPercent float , A50High float , A50Low float , A50Percent float, HKHigh float, HKLow float, HKPercent float);

1	create table everydayRecords(tablename char(30),high float, low float ,percent float, description varchar(200), ytdUSHigh float , ytdUSLow float , ytdUSPercent float , A50High float , A50Low float , A50Percent float, HKHigh float, HKLow float, HKPercent float);

create table with primary key

create table if1906_20190527 (id INT NOT NULL AUTO_INCREMENT,happentime DATETIME,lastprice double,b1 double,biVol int ,s1 double, s1Vol int,PRIMARY KEY(id ));

1	create table if1906_20190527 (id INT NOT NULL AUTO_INCREMENT,happentime DATETIME,lastprice double,b1 double,biVol int ,s1 double, s1Vol int,PRIMARY KEY(id ));

create table 最好这样写，能避免表已经存在，再建出错

CREATE TABLE IF NOT EXISTS blocks(id text primary_key,length numeric)

1	CREATE TABLE IF NOT EXISTS blocks(id text primary_key,length numeric)

一次创建多个表

2、执行sql脚本文件
方法一使用cmd命令执行(windows下，unix或linux在的其控制台下)
【Mysql的bin目录】\mysql –u用户名 –p密码 –D数据库<【sql脚本文件路径全名】，示例：
D:\mysql\bin\mysql –uroot –p123456 -Dtest<d:\test\ss.sql
注意：
A、如果在sql脚本文件中使用了use 数据库，则-D数据库选项可以忽略
B、如果【Mysql的bin目录】中包含空格，则需要使用“”包含，如：“C:\Program Files\mysql\bin\mysql” –u用户名 –p密码 –D数据库<【sql脚本文件路径全名】

方法二进入mysql的控制台后，使用source命令执行
Mysql>source 【sql脚本文件的路径全名】或 Mysql>\. 【sql脚本文件的路径全名】，示例：
source d:\test\ss.sql 或者 \. d:\test\ss.sql

create JiDie Table

create table JiDie_20190523 (happentime DATETIME,lastprice double,b1 double,biVol int ,s1 double, s1Vol int);

1	create table JiDie_20190523 (happentime DATETIME,lastprice double,b1 double,biVol int ,s1 double, s1Vol int);

create JiDie report table

create table JiDieRecords (tableName VARCHAR(100),starttime TIME,endtime TIME,startDownPoint double,endDownPoint double,continuetime TIME,FanTanHighPoint double,FanTanHighPointBetweenEndDown TIME);

1	create table JiDieRecords (tableName VARCHAR(100),starttime TIME,endtime TIME,startDownPoint double,endDownPoint double,continuetime TIME,FanTanHighPoint double,FanTanHighPointBetweenEndDown TIME);

找出含有最大值的row

select happentime , lastprice from if1903_20190214 where lastprice = (select max(lastprice) from if1903_20190214);

1	select happentime , lastprice from if1903_20190214 where lastprice = (select max(lastprice) from if1903_20190214);

check table column type

DESCRIBE tablename;

1	DESCRIBE tablename;

select limit count

select * from rb3 order by happentime desc limit 10;

1	select * from rb3 order by happentime desc limit 10;

delete all rows of table

DELETE FROM employees;

1	DELETE FROM employees;

c++ use mysql odbc to oper mysql DB

https://bobobobo.wordpress.com/2009/07/11/working-with-odbc-from-c/

alter table column type :

ALTER TABLE your_table
MODIFY COLUMN your_column DOUBLE NULL;

1 2	ALTER TABLE your_table MODIFY COLUMN your_column DOUBLE NULL;

delete row

delete from tb where ...

1	delete from tb where ...

常用的

select * from rb20181207 where hour(happentime)=9 or hour(happentime)=10 order by happentime desc limit 20;

1	select * from rb20181207 where hour(happentime)=9 or hour(happentime)=10 order by happentime desc limit 20;

delete table

DROP TABLE tablename;

1	DROP TABLE tablename;

某一列减去三小时

select happentime,DATE_SUB(happentime,interval 3 hour) from rb20181212 where hour(happentime)>22 limit 10;

1	select happentime,DATE_SUB(happentime,interval 3 hour) from rb20181212 where hour(happentime)>22 limit 10;

case when else end 的用法

select lastprice ,case when hour(happentime)>=21 then DATE_ADD(happentime,interval 10 hour) else happentime end as 'time' from rb20181212 where hour(happentime)=21  limit 30;

1	select lastprice ,case when hour(happentime)>=21 then DATE_ADD(happentime,interval 10 hour) else happentime end as 'time' from rb20181212 where hour(happentime)=21 limit 30;

show version

SHOW VARIABLES LIKE "%version%";

1	SHOW VARIABLES LIKE "%version%";

if forget root password (centos )

sudo /etc/init.d/mysqld stop

1	sudo /etc/init.d/mysqld stop

sudo mysqld_safe --skip-grant-tables &

1	sudo mysqld_safe --skip-grant-tables &

mysql -u root

1	mysql -u root

use mysql;

1	use mysql;

   UPDATE user SET Password = PASSWORD('newpwd')
   WHERE  User = 'root';

1 2	UPDATE user SET Password = PASSWORD('newpwd') WHERE User = 'root';

FLUSH PRIVILEGES;

1	FLUSH PRIVILEGES;

sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start

1 2	sudo /etc/init.d/mysql stop sudo /etc/init.d/mysql start

show all users

SELECT User, Host, Password FROM mysql.user;

1	SELECT User, Host, Password FROM mysql.user;

migrate mysql DB from one machine to another machine 备份去远程 banwagon , mysql pwd = Piercing77#

first backup current

mysqldump --user=root --password=MYSQLTB --opt shfuture > f:\shfutureBK.sql

1	mysqldump --user=root --password=MYSQLTB --opt shfuture > f:\shfutureBK.sql

如果是只导出单个表：

mysqldump --user=root --password=MYSQLTB --opt shfuture ag2006_20200518 > e:\MYSQLDB\ag2006_20200518.sql

1	mysqldump --user=root --password=MYSQLTB --opt shfuture ag2006_20200518 > e:\MYSQLDB\ag2006_20200518.sql

then copy sql file to remote machine , in my case is : /home/mysqlDB/shfutureBK.sql

create DB in remote machine

create database shfuture;

1	create database shfuture;

restore DB

mysql --user=root --password=Piercing77# shfuture < /home/mysqlDB/shfutureBK.sql

1	mysql --user=root --password=Piercing77# shfuture < /home/mysqlDB/shfutureBK.sql

c++如何连 mysql (centos 6)

先安装开发工具

sudo yum install mysql-devel

1	sudo yum install mysql-devel

安装后，头文件位于 /usr/include/mysql/mysql.h , 此全路径并不在default path , 但是 /usr/include 在，

所以，在 c文件里这么写：

#include <mysql/mysql.h>

1	#include <mysql/mysql.h>

编译 gcc -o showVersion source.c $(mysql_config –libs)

来自 https://stackoverflow.com/questions/3396181/gcc-wont-compile-and-run-mysql-c-libraries

linux connect and query

#include <iostream>
#include <mysql/mysql.h>
#include <stdio.h>
using namespace std;
#define SERVER "localhost"
#define USER "root"
#define PASSWORD "Piercing77#"
#define DATABASE "shfuture"

int main()
{
MYSQL *connect;
connect=mysql_init(NULL);
if (!connect)
{
cout<<"MySQL Initialization failed";
return 1;
}
connect=mysql_real_connect(connect, SERVER, USER, PASSWORD,
"shfuture" ,0,NULL,0);
if (connect)
{
cout<<"connection Succeeded\n";
}
else
{
cout<<"connection failed\n";
}
MYSQL_RES *res_set;
MYSQL_ROW row;
mysql_query (connect,"select * from if1901_20181221 limit 10;");
unsigned int i =0;
res_set = mysql_store_result(connect);
unsigned int numrows = mysql_num_rows(res_set);
while (((row= mysql_fetch_row(res_set)) !=NULL ))
{ //cout<<" %s\n",row[i] !=NULL?row[i] : "NULL";

 cout <<"\t | \t" << row[i] << "\t | \t";
 cout <<  row[i+1] <<  "\t |"<< endl;
}
mysql_close (connect);
return 0;
}

#include <iostream>

#include <mysql/mysql.h>

#include <stdio.h>

using namespace std;

#define SERVER "localhost"

#define USER "root"

#define PASSWORD "Piercing77#"

#define DATABASE "shfuture"

int main()

{

MYSQL *connect;

connect=mysql_init(NULL);

if (!connect)

{

cout<<"MySQL Initialization failed";

return 1;

}

connect=mysql_real_connect(connect, SERVER, USER, PASSWORD,

"shfuture" ,0,NULL,0);

if (connect)

{

cout<<"connection Succeeded\n";

}

else

{

cout<<"connection failed\n";

}

MYSQL_RES *res_set;

MYSQL_ROW row;

mysql_query (connect,"select * from if1901_20181221 limit 10;");

unsigned int i =0;

res_set = mysql_store_result(connect);

unsigned int numrows = mysql_num_rows(res_set);

while (((row= mysql_fetch_row(res_set)) !=NULL ))

{ //cout<<" %s\n",row[i] !=NULL?row[i] : "NULL";

cout <<"\t | \t" << row[i] << "\t | \t";

cout << row[i+1] << "\t |"<< endl;

}

mysql_close (connect);

return 0;

}

linux insert

string player="INSERT INTO Player (PlayerID, Name, Salary, StartDate) values (plID, name, salary, startdate)";
int query_state=mysql_query(conn, instrument.c_str());

1 2	string player="INSERT INTO Player (PlayerID, Name, Salary, StartDate) values (plID, name, salary, startdate)"; int query_state=mysql_query(conn, instrument.c_str());

从一个表select 出 multi rows , insert 到另外一个表

insert into if1901_20181227(happentime,lastprice,b1,biVol,s1,s1Vol) select happentime,lastprice,b1,biVol,s1,s1Vol from if1901_20181226 where happentime > '2018-12-27';

1	insert into if1901_20181227(happentime,lastprice,b1,biVol,s1,s1Vol) select happentime,lastprice,b1,biVol,s1,s1Vol from if1901_20181226 where happentime > '2018-12-27';

date add

SELECT DATE_ADD("2019-02-24 10:27:00", INTERVAL 9 HOUR);

1	SELECT DATE_ADD("2019-02-24 10:27:00", INTERVAL 9 HOUR);

时间在某个区间

select * from if1904_20190320 where time(happentime)  > '09:53'  and time(happentime)  < '10:05' ;

1	select * from if1904_20190320 where time(happentime) > '09:53' and time(happentime) < '10:05' ;

插入每日总结表

insert into everydayRecords (tablename,high,low,percent,description,ytdUSHigh,ytdUSLow,ytdUSPercent,A50High,A50Low,A50Percent,HKHigh,HKLow,HKPercent) values('if1904_20190329',3895.4,3732.8,4.15,'高开高走2点半后小回落',25743.41,25576.69,0.36,13118.08,12598.10,3.94,29100.69,28761.88,0.96);

1	insert into everydayRecords (tablename,high,low,percent,description,ytdUSHigh,ytdUSLow,ytdUSPercent,A50High,A50Low,A50Percent,HKHigh,HKLow,HKPercent) values('if1904_20190329',3895.4,3732.8,4.15,'高开高走2点半后小回落',25743.41,25576.69,0.36,13118.08,12598.10,3.94,29100.69,28761.88,0.96);

在已经存在的表上，加自增id

 ALTER TABLE users ADD id int NOT NULL AUTO_INCREMENT primary key

1	ALTER TABLE users ADD id int NOT NULL AUTO_INCREMENT primary key

从一个表往另一个表插数

insert into JiDie_20190523 SELECT * FROM if1906_20190523 WHERE time(happentime)>='09:29:00' and time(happentime)<='10:53:24'

1	insert into JiDie_20190523 SELECT * FROM if1906_20190523 WHERE time(happentime)>='09:29:00' and time(happentime)<='10:53:24'

存储过程

UntilNowSummary — 显示至今为止的报告

存储过程规则 mysql 5.1 :

表名和数据库名不能用变量代替，在存储过程里

手册

https://docs.oracle.com/cd/E19078-01/mysql/mysql-refman-5.1/

django 专用数据库

DjangoDB

从一个数据库往另一个数据库的表复制，目的表有主键，来源表没有主键

 insert into if1906_20190527 (happentime,lastprice,b1,biVol,s1,s1Vol) select happentime,lastprice,b1,biVol,s1,s1Vol  from shfuture.if1906_20190527;

1	insert into if1906_20190527 (happentime,lastprice,b1,biVol,s1,s1Vol) select happentime,lastprice,b1,biVol,s1,s1Vol from shfuture.if1906_20190527;

下载 mysql 5.7 版本

https://www.cnblogs.com/tyhj-zxp/p/6693046.html

oracle 登陆： gmail , Curmudgeon77

如何允许远程登陆

在 /etc/my.cnf 加

port = 3306

1	port = 3306

远程登陆显示：

Host 'xxx.xx.xxx.xxx' is not allowed to connect to this MySQL server

1

Host 'xxx.xx.xxx.xxx' is not allowed to connect to this MySQL server

解决方法：在 mysql 数据库的 user 表加一个新用户 , XXX mean password

mysql> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'XXX';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'monty'@'%' IDENTIFIED BY 'XXX';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'XXX';

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' WITH GRANT OPTION;

Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'monty'@'%' IDENTIFIED BY 'XXX';

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' WITH GRANT OPTION;

Query OK, 0 rows affected (0.00 sec)

mysql>

允许某个ip访问

GRANT ALL ON shfuture.* TO root@192.168.3.120 IDENTIFIED BY ‘MYSQLTB’;