1. Tạo cơ sở dữ liệu MySQL

create database  struts2_db;
grant all on struts2_db.* to 'root'@'127.0.0.1' identified by '123456';
grant all on struts2_db.* to 'root'@'localhost' identified by '123456';
------------
grant all on *.* to 'root'@'192.168.1.132' identified by '123456';
mysql -h127.0.0.1 -uroot -p123456 < data.sql;
-------
grant all on struts2_db.* to 'root'@'localhost' identified by '123456';
grant all on struts2_db.* to 'root'@'127.0.0.1' identified by '123456';
2. Một vài Mysql command:
  • mysql -uroot -pexo
  • shell> mysql -u root
  • mysql> UPDATE mysql.user SET Password = PASSWORD('nbuser') WHERE User = 'root';
  • mysql> FLUSH PRIVILEGES;
  • sudo chmod 777 -R /var/lib/mysql/
  • grant all privileges on test2.* to gtest@"localhost" identified by 'gtest00'
  • ALTER TABLE t_pdata ADD is_sales int(11) DEFAULT '0'
  • mysqladmin -uroot -pexo variables | grep dir
  • ll /var/lib/mysql/ -tr
=> see the changes in mysql to know a mysql command running or not.
  • ps ax|grep mysql => to see mysql processes detail
  • ls /var/lib/mysql/plf_jcr/
  • enable or disable the general query log mysql to show low query
    mysql -uroot -pgtngtn -e "set global general_log_file='/var/lib/mysql/all.log'; set global general_log='ON'; "
    mysql -uroot -pexo -e "set global general_log_file='/var/lib/mysql/all.log'; set global general_log='ON';"
  • Login to mysql by command:
    mysql -uroot -pexo
  • CREATE INDEX IDX_NAME ON affablebean.product (name);
  • SELECT * FROM affablebean.product  prod where ID in ('5074','30','2552') ORDER BY FIELD(prod.ID,'5074','30','2552');
  • SELECT s.PROJECT_ID,t.TASK_ID,t.TITLE FROM plf_jcr.TASK_TASKS t inner join plf_jcr.TASK_STATUS s on t.STATUS_ID = s.STATUS_ID where s.PROJECT_ID in (SELECT PROJECT_ID from plf_jcr.TASK_PROJECTS where NAME NOT like '%bcdusers0098%' and NAME like '%-0%' and NAME like '%manager%') group by s.PROJECT_ID asc INTO OUTFILE '/tmp/1/00.txt';
  • SELECT s.PROJECT_ID,t.TASK_ID,t.TITLE, mod(t.TASK_ID,50) as aaa FROM plf_jcr.TASK_TASKS t inner join plf_jcr.TASK_STATUS s on t.STATUS_ID = s.STATUS_ID where s.PROJECT_ID in (406,407)  group by s.PROJECT_ID,aaa INTO OUTFILE '/tmp/labeledtask_10.txt';
  • select count(*) from product where STATUS IS NULL;
  • execute-a-mysql-command-from-a-shell-script >> mysql -h plfcap-mysql-server -uroot -ptest_control < update_db_connections.sql >> mysql -h plfcap-mysql-server -uroot -ptest_control database -e "SELECT * FROM blah WHERE foo='bar';" >> mysql -h plfcap-mysql-server -uroot -ptest_control -e >> mysql -h localhost -uroot -ptest_control -e "UPDATE TASK_TASKS SET END_DATE='2015-11-24 13:30:00', START_DATE='2015-11-24 13:00:00' WHERE END_DATE='2015-09-29 13:30:00' and START_DATE='2015-09-29 13:00:00'" plf_jcr
  • Some Where clause >> where NAME NOT like '%bcdusers0098%' and NAME like '%manager%'
  • Các câu lệnh khác:
    sudo service mysql stop ll /home/mysql/mysql/ pushd /home/mysql/mysql/ mv mysql ../mysql_db /home/mysql/mysql$ rm -rf * dirs sudo chmod 777 -R /home/mysql rm -f mysql mv ../mysql_db mysql sudo service mysql start sudo tail -100 /var/log/mysql/error.log mysql -uroot -p123456 use struts2_db; select * from catalog; /home/mysql/mysql$ popd cd /var/lib/mysql/backup-databases/EMPTY_DATASET

3. Import/Export MysqlDB
When backup mysql, it will dump some DB with options:
add-drop-database > data.sql
with these option, mysql will use only two(some) db names above, and it will delete the old db when importing.
Save query result to file:
mysql> SELECT order_id,product_name,qty FROM orders INTO OUTFILE '/tmp/orders.txt'
mysql> select TASK_ID,TITLE,CREATED_BY from TASK_TASKS where TASK_ID > 29 and MOD(TASK_ID,2520) < 50 INTO OUTFILE '/tmp/deletetasks.txt';
Import:
mysql -h127.0.0.1 -uroot -p123456 < data.sql;
Export query result to FILE:
mysql> select * from CATALOG INTO OUTFILE '/home/administrator/tmp/orders.txt';
Query OK, 8000 rows affected (0.00 sec)

4. Cài đặt mysql ở HĐH ubuntu/centos & others:
Ubuntu
  • sudo rm -rf /var/cache/apt/archives/*
  • sudo apt-get purge mysql* (sudo rm -rf /var/lib/mysql,sudo rm -rf /etc/mysql)
  • sudo apt-get autoremove
  • sudo apt-get autoclean (sudo apt-get dist-upgrade)
  • sudo dpkg --configure -a
  • sudo apt-get update
  • sudo apt-get install -f
  • sudo apt-get install mysql-client mysql-server
  • sudo apt-get install mysql-workbench
CentOS
  • yum install mysql-server
  • yum install mysql-bench
  • yum install mysql-devel
  • yum install php php-mysql php-common php-gd php-mbstring php-mcrypt php-devel php-xml
  • chkconfig httpd on OR /sbin/chkconfig httpd on
  • chkconfig mysqld on OR /sbin/chkconfig mysqld on OR /etc/init.d/mysqld start
  • chown -R mysql:mysql /var/lib/mysql/
  • service --status-all | grep mysqld =>'mysql dead but subsys locked'
  • cd /var/lock/subsys rm -f mysqld /etc/rc.d/init.d/mysqld restart
  • tar jxf phpMyAdmin-3.4.8-all-languages.tar.bz2
  • mv phpMyAdmin-3.4.8-all-languages phpmyadmin
  • cd phpmyadmin
  • cp config.sample.inc.php config.inc.php
  • gedit config.inc.php
  • $cfg['Servers'][$i]['auth_type'] = ‘http‘; # default is cookies
  • service httpd restart