[Mysql] Mysql command

Các lệnh mysql

1. Show current database is selected:

SELECT DATABASE() FROM DUAL;

2. Import/Export mysql

https://www.digitalocean.com/community/tutorials/how-to-import-and-export-databases-and-reset-a-root-password-in-mysql
Note:
a. Tránh lock bảng
--lock-tables=false --compact
b. Sử dụng lệnh source để xem kết quả import trên từng dòng
https://dev.mysql.com/doc/refman/5.7/en/mysql-batch-commands.html

3.  View a list of mysql users and their privileges

http://xmodulo.com/how-to-view-list-of-mysql-users-and-their-privileges.html

• To get a list of MySQL users

mysql> select user,host from mysql.user;

4. size of tables

http://stackoverflow.com/questions/9620198/how-to-get-the-sizes-of-the-tables-of-a-mysql-database

You can use this query to show the size of a table (although you need to substitute the variables first):
SELECT 
    table_name AS `Table`, 
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
WHERE table_schema = "$DB_NAME"
    AND table_name = "$TABLE_NAME";

or this query to list the size of every table in every database, largest first:
SELECT 
     table_schema as `Database`, 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;

[Mysql] install Percona Mysql

Hướng dẫn cài đặt Percona + Mysql

References

1. https://www.percona.com/doc/percona-xtrabackup/2.2/intro.html

[Centos] netstat hiển thị port khi scan

Hiển thị port đang listen trên Centos

References

1. http://unix.stackexchange.com/questions/140021/how-to-close-ports-in-linux

2. Nmap is a great port scanner, but sometimes you want something more authoritative. You can ask the kernel what processes have which ports open by using the netstat utility:
   

   me@myhost:~$ sudo netstat -tlnp
   Active Internet connections (only servers)
   Proto Recv-Q Send-Q Local Address   Foreign Address   State    PID/Program name
   tcp        0      0 127.0.0.1:53    0.0.0.0:*         LISTEN   1004/dnsmasq    
   tcp        0      0 0.0.0.0:22      0.0.0.0:*         LISTEN   380/sshd        
   tcp        0      0 127.0.0.1:631   0.0.0.0:*         LISTEN   822/cupsd       
   tcp6       0      0 :::22           :::*              LISTEN   380/sshd        
   tcp6       0      0 ::1:631         :::*              LISTEN   822/cupsd       
   

   The options I have given are:
   

• -t TCP only
• -l Listening ports only
• -n Don't look up service and host names, just display numbers
• -p Show process information (requires root privilege)

[Centos] How to edit sudoers file - cấp quyền sudo cho user command

How to edit sudoers file

References

1. https://www.digitalocean.com/community/tutorials/how-to-edit-the-sudoers-file-on-ubuntu-and-centos 

Description

Bài viết này hướng dẫn cách cấu hình cấp quyền sudo cho user trên các command.

[Mysql] Guide install mysql

Guide Install MySQL

I.References

1. http://daoanhduc.blogspot.com/2014/09/guide-install-mysql.html
2. http://dev.mysql.com/doc/refman/5.7/en/installing-source-distribution.html

II.Hướng dẫn cài đặt

1. Guide 1: install full step (2)

set sql_mode = NO_ENGINE_SUBSTITUTION;
#Guideline MySQL
###Install gmake
tar zxvf cmake-VERSION.tar.gz
cd cmake-VERSION
./bootstrap
gmake
gmake install

###Required:
rpm -ivh ncurses-devel-5.7-3.20090208.el6.x86_64.rpm

###Install mysql
groupadd mysql
useradd -r -g mysql mysql
passwd mysql
tar zxvf mysql-VERSION.tar.gz
cd mysql-VERSION
cmake .
make -j4
make install

cd /usr/local/mysql
chown -R mysql .
chgrp -R mysql .
bin/mysql_install_db --user=mysql
chown -R root .
chown -R mysql:mysql /var/lib/mysql/
bin/mysqld_safe --user=mysql &
bin/mysql_secure_installation
cp support-files/mysql.server /etc/init.d/mysql.server
chkconfig mysql.server on


#Cau hinh /etc/my.cnf  (luu y cau hinh server-id, auto_increment_increment, auto_increment_offset
#Restart lai ung dung
#Grant quyen tuong ung

GRANT SELECT, INSERT, DELETE, UPDATE ON <database name>.* TO '<user>'@'<ip>' IDENTIFIED BY 'ke-34#$klZ' WITH GRANT OPTION;


GRANT SELECT, INSERT, DELETE, UPDATE, EVENT, EXECUTE ON <database name>.* TO '<user>'@'<ip>' IDENTIFIED BY 'wurfl-383#$k5Z' WITH GRANT OPTION;


#Cau hinh replication

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'<ip>' IDENTIFIED BY '<password>'; 
CHANGE MASTER TO MASTER_HOST='<ip>', MASTER_USER='<user>', MASTER_PASSWORD='<user>', MASTER_PORT=3306,MASTER_LOG_FILE='pe-mysql3-bin.000001', MASTER_LOG_POS=120, MASTER_CONNECT_RETRY=10;


CHANGE MASTER TO MASTER_HOST='<ip>', MASTER_USER='<user>', MASTER_PASSWORD='slave', MASTER_PORT=3306,MASTER_LOG_FILE='pe-mysql5-bin.000001', MASTER_LOG_POS=120, MASTER_CONNECT_RETRY=10;
CHANGE MASTER TO MASTER_HOST='<ip>', MASTER_USER='<user>', MASTER_PASSWORD='slave', MASTER_PORT=3306,MASTER_LOG_FILE='pe-mysql6-bin.000001', MASTER_LOG_POS=120, MASTER_CONNECT_RETRY=10;

2. Guide 2: install using Generic binaries(2)

download mysql-5.7.10-linux-glibc2.5-x86_64.tar
tar -xvf  mysql-5.7.10-linux-glibc2.5-x86_64.tar
tar -xzvf mysql-5.7.10-linux-glibc2.5-x86_64.tar.gz
mv mysql-5.7.10-linux-glibc2.5-x86_64 /u01/app/vas_dealer/run/mysql-5.7.10
cd /u01/app/vas_dealer/run/mysql-5.7.10
#Copy file my.cnf cu vao thu muc mysql-5.7.10
bin/mysqld --defaults-file=/u01/app/vas_dealer/run/mysql-5.7.10/my.cnf --initialize
bin/mysqld_safe --defaults-file=/u01/app/vas_dealer/run/mysql-5.7.10/my.cnf
set password = password('newpass');
bin/mysql_secure_installation -u root -p

bin/mysql_upgrade --defaults-file=/u01/app/vas_dealer/run/mysql-5.7.10/my.cnf -u root -p

3.Guide 3: install Mysql from source (3)

1. install M4

http://www.gnu.org/software/m4/m4.html

2,install libiconv

https://www.gnu.org/software/libiconv/

3.instal Ncurses

https://www.gnu.org/software/ncurses/

4. install Bison

https://www.gnu.org/software/bison/

5. install cmake

https://cmake.org/

6. install libaio

yum install libaio

7. Install Mysql

[Mysql] Guide upgrade MySQL

Guide upgrade MySQL

reference: 

1. http://nghialuong.blogspot.com/2015/12/guide-install-mysql.html
2. http://dev.mysql.com/doc/refman/5.7/en/mysql-upgrade.html

Step 1: install mysql new version (1)
Thực hiện cài đặt theo hướng dẫn trong link 1
Step 2: upgrade mysql (2)