MySql Tips

Optimization: http://dev.mysql.com/doc/refman/5.1/en/optimization.html
HA: http://dev.mysql.com/doc/refman/5.1/en/ha-overview.html
Replication: http://dev.mysql.com/doc/refman/5.1/en/replication.html
Cluster: http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster.html

log in:
mysql -h localhost -u root -pyourpassword

set password:
set password for 'root'@'localhost' = password('123456');

create db:
CREATE DATABASE `newone` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

add privileges:
CREATE USER 'root'@'192.168.0.0.1';
GRANT ALL PRIVILEGES ON * . * TO 'root'@'192.168.0.0.1' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
flush privileges;

add user:
CREATE USER 'hello'@'192.168.0.2' IDENTIFIED BY '***';
GRANT ALL PRIVILEGES ON * . * TO 'hello'@'192.168.0.2' IDENTIFIED BY '***' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
flush privileges;

check user:
mysql> select Host,User from mysql.user;

export db(or you can use mysql enterprise backup<mysqlbackup, hot/warm/cold>):
mysqldump -u root -pyourpassword --no-data --default-character-set=utf8 test>aaa.sql #--no-data export only structures

import db:
mysql -h localhost -u root -pyourpassword dbname</root/db.sql

show storage engine of specified table

SELECT `ENGINE` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA`='ovs' AND `TABLE_NAME`='Mgr_NetworkFileServer';

SQL:

create table wp_posts_bak as select * from wp_posts;

select post_title,post_status,post_name from wp_posts where post_content like "%<pre>%";

update wp_posts set post_content=replace(post_content,'<blockquote>','<br /><i>');

DELIMITER // #when you need define some functions and met error "ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1". You'll also need type END// instead of END;. Switch delimiter back to ; using DELIMITER ;

select length(post_content),char_length(post_content) from wp_posts;

PS:

1. Below are comparision about mysql storage engine MyISAM & InnoDB - https://www.pureweber.com/article/myisam-vs-innodb/ & http://coolshell.cn/articles/652.html
2. Here is about binlog format(row based/statement based/mixed, on slave it's relay logs) - http://dev.mysql.com/doc/refman/5.7/en/binary-log-setting.html
3. We can use mysql proxy to make read/write separation. And we can use Mysql cluster for distributed HA/High redundant topology(shared nothing, need high bandwidth, RAC is shared disk architecture). For Oracle DB, we can use different service names for read/write separation.