ASP.NET 連接Mysql 語法記錄

string ConnString = System.Configuration.ConfigurationManager.ConnectionStrings["MysqlConnectionString"].ToString();
string sql = "";

MySqlConnection conn = new MySqlConnection(ConnString);
MySqlCommand cmd = new MySqlCommand();
MySqlDataAdapter da = new MySqlDataAdapter();

cmd.Connection = conn;

sql = "INSERT INTO table (var1,var2) VALUES (@v1,@v2);SELECT LAST_INSERT_ID()";
cmd.CommandText = sql;

da.SelectCommand = cmd;
da.SelectCommand.Parameters.AddWithValue("@v1", 1);
da.SelectCommand.Parameters.AddWithValue("@v2", "AA");

DataTable dt = new DataTable();
da.Fill(dt);

last_insert_id = dt.Rows[0][0].ToString();

MYSQL on MAC 設定筆記

/usr/local/mysql/my.cnf

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

MySQL5.0.2版本中的The Server SQL Mode 中新加了幾種 sql_mode,並且在sql的my.ini文件中的sql-mode 默認增加了其中的二種,一個是STRICT_TRANS_TABLES,就是導致auto_increament失敗的mode,這個sql_mode主要用于 當一個值不能插入到表中是,則産生一個錯誤而不是一個警告並終止會話。

#有時太小會導致寫入失敗,預設值好像才1M
max_allowed_packet = 20M

MySQL Replication Master-Slave筆記

Master(my.cnf)192.168.1.203
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log

binlog_format = MIXED
要同步的DB
binlog_do_db = test
binlog_do_db = test2
innodb_flush_log_at_trx_commit=1
sync_binlog=1

mysql -u root -p
新增帳號給SLAVE同步使用
mysql>GRANT REPLICATION SLAVE ON *.* TO 'rep' IDENTIFIED BY 'pass';
or
mysql -u root -p -e "GRANT REPLICATION SLAVE ON *.* TO 'rep' IDENTIFIED BY 'pass';"
先鎖定資料庫
mysql -u root -p -e "FLUSH TABLES WITH READ LOCK;"
mysql -u root -p -e "SHOW MASTER STATUS\G;"
+------------------+----------+--------------+------------------+
| File Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      107 | test         |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
記下File與Position，等下會用到
quit

匯出要同步的那個資料庫
mysqldump -uroot -p --flush-logs --opt --master-data test > test.sql

解除鎖定

mysql -u root -p -e "UNLOCK TABLES;"

此時MASTER上的工作結束。

Slave(my.cnf)192.168.1.204

server-id               = 2

要同步的DB
replicate_wild_do_table = test.%

mysql -uroot -p -e "STOP SLAVE; RESET SLAVE;"

mysql -uroot -p -e "CHANGE MASTER TO MASTER_HOST='192.168.1.204',MASTER_USER='rep',MASTER_PASSWORD='pass',
MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=107;"

mysql -uroot -p -e "START SLAVE;"

檢視MASTER的狀態
mysql -uroot -p -e "SHOW MASTER STATUS;"
+------------------+----------+--------------+------------------+
| File|Position| Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

檢視SLAVE的狀態
mysql -uroot -p -e "SHOW SLAVE STATUS\G;"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.204
Master_User: rep
Master_Port: 3306
......
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......

運作正常，此時可到MASTER上新增資料看是否立即同步至SLAVE

FTP上傳備份資料Script

將備份檔案以7zip壓縮並上傳FTP的Script，記綠一下。

backup.sh

#!/bin/bash
bd=$(date +%Y%m%d)
cd /where you want to backup/

tar pcvf - directoryname|7za a -si /bak/directoryname.${bd}.tar.7z

mysqldump -uusername -ppassword dbname > /bak/dbname.${bd}.sql
svnadmin dump /home/svn/repository/project > /bak/project.dump

cd /bak/

HOST='IP'
USER='username'
PASS='password'
ftp -ivn $HOST << EOF
user $USER $PASS
bin
put backupfilename
quit
EOF


Note:FTP常用指令

ls     查看 Server 端的目錄或檔案
pwd    查看 Server 端目前所在的目錄
cd     變更 Server 端目前的目錄
cdup   變更 Server 端目前的目錄到上一目錄
lls    查看 Local 端的目錄或檔案
lcd    變更 Local 端目前的目錄
asc    設定傳輸模式為文字檔方式
bin    設定傳輸模式為二進位檔方式
get    將 Server 端的檔案拷貝至 Local 端現在目錄下
mget   拷貝多個 Server 端的檔案至 Local 端現在目錄下
put    將 Local 端的檔案拷貝至 Server 端現在目錄下
mput   拷貝多個 Local 端的檔案至 Server 端現在目錄下
delete 刪除 Server 端的檔案
mkdir  在 Server 端建立目錄
rmdir  刪除 Server 端的目錄
!      shell 指令
!ls    查看 Local 端的目錄或檔案
prompt 變換交談模式 (on/off)，
?,help 指令使用說明
bye    結束 FTP

LAMP安裝備忘(Linux Apache Mysql Php) on Debian

apt-get install apache2 mysql-server php5 php5-mysql

apache2設定

編輯 /etc/apache2/apache2.conf or httpd.conf
加上 EnableSendfile Off

編輯 /etc/apache2/apache2.conf
加上 ServerName 127.0.0.1

mysql設定

編輯 /etc/mysql/my.cnf
修改 bind-address = 127.0.0.1 => #bind-address = 127.0.0.1
[mysqld]之下加入(跟效能調校有關)
max_connect_errors = 1844674407370954751
connect_timeout = 20
skip-name-resolve
slave_net_timeout = 30


建帳號、新增使用者
mysql> grant all privileges on *.* to 'vega'@'localhost' identified by 'vega' with grant option;

mysql> grant all privileges on *.* to 'vega'@'%' identified by 'vega' with grant option;

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON dbname.* to 'vega'@'%' identified by 'vega';

更新權限
mysql>flush privileges;

改密碼
mysql>update user set Password=PASSWORD('new_pass') where user='root';

MYSQL 5.7版已無Password欄位
update user set authentication_string=password('1111') where user='root';

看權限

SHOW GRANTS FOR 'vega'@'%';

ASP筆記(連接MYSQL)

先安裝MYSQL ODBC

set conn = server.CreateObject("ADODB.Connection")
conn.open
"DRIVER={};
SERVER = 192.168.X.X;
DATABASE=test;
UID=;
PASSWORD=;
OPTION=3"

str = "SELECT * FROM ..."
set rs = ServerCreateObject("ADODB.recordset")
rs.open str,conn,1,A   A=1(讀) or 3(寫)

rs.Fields.Count
rs(i).Name
rs(i)
rs('column name')
rs.RecordCount
rs.BOF
rs.EOF
rs.MoveFirst
rs.MoveNext
rs.GetRows
rs.Properties.Count
rs.Properties(item).Name
rs.Properties
rs.colse()

PHP之PDO於MYSQL之[新增][修改][刪除]

[新增]

$sql = "INSERT INTO table_name(col1,col2,......coln) VALUES($var1,$var2,......$varn)";

$result = $db->exec($sql);

[修改]

$sql = "UPDATE dbname SET col1='$var1',col2='$var2',......coln='$varn' WHERE ......";
$result = $db->prepare($sql);
$result->execute();

[刪除]

$sql = "DELETE FROM dbname WHERE id=".$id."";
$result = $db->exec($sql);

[$db->prepare另外用法]

$sql = "UPDATE table_name SET col1=? WHERE col2=? AND col3=?";
$result = $db->prepare($sql);
$result->execute(array($var1,$var2,$var3));

Appserv安裝備忘

MYSQL
1.建帳號、新增使用者
mysql> grant all privileges on *.* to 'vega'@'localhost' identified by 'vega' with grant option;
mysql> grant all privileges on *.* to 'vega'@'%' identified by 'vega' with grant option;

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON dbname.* to 'vega'@'%' identified by 'vega';


更新權限
mysql>flush privileges;

2.改密碼
mysql>update user set Password=PASSWORD('new_pass') where user='root';

MYSQL 5.7版已無Password欄位
update user set authentication_string=password('1111') where user='root';


web root:
C:\AppServ\www

modify C:\WINDOWS\php.ini
打開output_buffering
output_buffering = Off -> output_buffering = On
打開PDO for mysql
;extension=php_pdo_mysql.dll -> extension=php_pdo_mysql.dll

記憶體限制調大
memory_limit = 8M 改為 32M

設定時區
date.timezone = "Asia/Taipei"

關閉session warn
session.bug_compat_42 = 0

session.bug_compat_warn = 0