MySQL how to: create

Showing posts with label create. Show all posts

Wednesday, January 29, 2014

MySQL Basics

Overview

This Post is to give you the basic commands in mysql so you can do basic administration.
This is based on mysql installed on a linux host

How to start/Stop/Restart MySQL

service mysql start

service mysql stop

service mysql restart

How to check MySQL instance status

mysql -uroot -pyour_password -e STATUS

--------------
mysql  Ver 14.14 Distrib 5.5.27, for Linux (x86_64) using  EditLine wrapper

Connection id:          70
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.5.27-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 14 hours 47 min 44 sec

Threads: 1  Questions: 546080  Slow queries: 0  Opens: 19103  Flush tables: 27  Open tables: 2079  Queries per second avg: 10.252
--------------

How to see the MySQL processes in linux

ps -ef | grep -i mysql

root     16111     1  0 08:55 ?        00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/prodmysql02.pid
mysql    16540 16111  1 08:55 ?        00:12:45 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/prodmysql02.err --pid-file=/var/lib/mysql/prodmysql02.pid --socket=/var/lib/mysql/mysql.sock

How to connect to the instance mysql

mysql -uusername -pyour_password

How to create a database

The simpliest way is

create database my_database;

or to avoid error message if the database already exist

create database if not exists my_database;

How to drop a database

Just a warning, when dropping a database, the system won't ask you to confirm if you want to drop it.

drop database my_database;

How to connect or change database

use rcinet

use rcinet;

How to list the databases in MySQL

show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
10 rows in set (0.01 sec)

How to check the size of the databases

This is a select that was given to me by one of my friend when I was looking for this information. I kept it and scripts it.

SELECT table_schema "db_name",

sum(data_length+index_length) /1024/1024 "db_size in Mb"

FROM information_schema.tables

GROUP BY 1;

+--------------------+---------------+
| db_name            | db_size in Mb |
+--------------------+---------------+
| information_schema |    0.00878906 |
| mysql              |    0.62183380 |
| performance_schema |    0.00000000 |
+--------------------+---------------+
9 rows in set (10.94 sec)

How to create a table in MySQL

CREATE TABLE my_table (

mycol1 INT(5) NOT NULL DEFAULT 0,

mycol2 CHAR(100) DEFAULT NULL,

PRIMARY KEY (mycol1)

);

How to show the tables in a database

show tables;

+-------------------------+
| Tables_in_xmas_card2012 |
+-------------------------+
| my_table                |
+-------------------------+
1 row in set (0.00 sec)

How to create a user in MySQL

create user account1 identified by 'your_password';

grant all privileges on *.* to account1;

How to list the users in mysql

select user,host from mysql.user;

+-------------+--------------------------------+
| user        | host                           |
+-------------+--------------------------------+
| root        | %                              |
| root        | 127.0.0.1                      |
| dbaadmin    | localhost                      |
| root        | localhost                      |
+-------------+--------------------------------+
15 rows in set (0.02 sec)

How to show the status of a Master or Slave/Replica in MySQL

show master status\G   This is on a master instance

*************************** 1. row ***************************
            File: log-bin.000078
        Position: 64150203
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

show slave status\G    This is on a Slave/Replica instance

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: xxx.xxx.xxx.xxx
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log-bin.000056
          Read_Master_Log_Pos: 986876833
               Relay_Log_File: mysql_relay-bin.000130
                Relay_Log_Pos: 64787846
        Relay_Master_Log_File: log-bin.000056
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 986876833
              Relay_Log_Space: 64972302
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 123
1 row in set (0.00 sec)

How to flush the data to disk and set the database in read only

flush tables with read lock;

How to remove the read only on a database

unlock tables;

How to check and repair a database

mysqlcheck -c your_database  -uroot -pyour_password --auto-repair

Character set in MySQL

Character sets are mainly used to tell MySQL what to store in MySQL dependng of the language. Character set can be defined at

the server level
the database level
the table level
the column level

show character set;

+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| cp850    | DOS West European           | cp850_general_ci    |      1 |
| hp8      | HP West European            | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                    | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |
| cp1250   | Windows Central European    | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                 | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 |
| macce    | Mac Central European        | macce_general_ci    |      1 |
| macroman | Mac West European           | macroman_general_ci |      1 |
| cp852    | DOS Central European        | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode              | utf16_general_ci    |      4 |
| cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode              | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset       | binary              |      1 |
| geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |
+----------+-----------------------------+---------------------+--------+
39 rows in set (0.03 sec)

Collation in MySQL

Collation in MySQL is used for how the data is sort. Each character has a position in the ordering process.

show character set;

+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| cp850    | DOS West European           | cp850_general_ci    |      1 |
| hp8      | HP West European            | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                    | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |
| cp1250   | Windows Central European    | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                 | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 |
| macce    | Mac Central European        | macce_general_ci    |      1 |
| macroman | Mac West European           | macroman_general_ci |      1 |
| cp852    | DOS Central European        | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode              | utf16_general_ci    |      4 |
| cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode              | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset       | binary              |      1 |
| geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |
+----------+-----------------------------+---------------------+--------+
39 rows in set (0.01 sec)

Thursday, August 23, 2012

How to create a replication (Master/Slave) in MySQL

Overview

This post is based on my work on Master/Slave environments creation.
I Discover 2 way of creating a Slave/Replica from a Master.
the first way is by using a cold backup of the/var/lib/mysql folder from the Master which means that you will have to have a down time on your application if you want a consistent backup.
The second solution that I found out not long ago during a Slave/Replica crash that I couldn't resync with the master since they were out of sync since about 60 days. That second option for creating a Slave/Replica from a Master is by using a mysqldump backup using to proper options.
So here are the 2 solutions to share.

These 2 procedures assume that both Master and Slave/Replica hosts have already been install with MySQL.

If not you can follow this link to install MySQL How to Install MySQL on linux using RPM

NOTE:

Assuming
master host = 192.168.10.1
slave/replica host = 192.168.10.2

Create a Slave/Replica using a cold backup (requires applications down time)

1. Create a replication account on the Master

create user repl@'192.168.10.1' IDENTIFIED BY 'yourpassword';
GRANT REPLICATION SLAVE, REPLICATION CLIENT 
        ON *.*
        TO repl@'192.168.10.1' [ IDENTIFIED BY 'yourpassword' ];

GRANT REPLICATION client, REPLICATION CLIENT 
        ON *.*
        TO repl@'192.168.10.1' [ IDENTIFIED BY 'yourpassword' ];

2. Modify the /etc/my.cnf file on the Master

under [mysqld] 

expire_logs_days=2 #  purge bin logs older than 2 days
log-bin=/var/lib/mysql/log-bin # bin location (use full path)
server-id=100 # unique id for the master (default is 1)
sync_binlog=1 # to sync the binlogs

if you are using InnoDB it is recommended to use

innodb-flush-log-at-trx-commit=1 # This will flush the log write
innodb-support-xa=1 # mysql 5.5 and later

3. Take note of the Master current bin log and position

This information will be needed when setting the Slave/Replica later one.

show master status\G

*************************** 1. row ***************************
            File: log-bin.000056
        Position: 425307404
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.01 sec)

4. Restart your Master to enable the new parameters

You need to restart the Master in order to take the new parameters from themy.cnf.

service mysql restart

5. Do a cold backup of the Master

This backup is a cold backup of the /var/lib/mysql folder using the cp command.

service mysql stop
cp -Rp /var/lib/mysql  /tmp/.
cd /tmp
tar -zcvf mysql.tar mysql
rm -Rf /tmp/mysql

6. Restart the master

service mysql start

7. Transfer the backup to the Slave/Replica host using SCP

cd /tmp
scp mysql.tar root@192.168.10.2:/tpm/.

8. Stop MySQL on the slave host (192.168.10.2)

service mysql stop

9. Remove the content of /var/lib/mysql on the slave

I highly suggest that you take a backup of /var/lib/mysql before you erase it.

cd /var/lib/mysql
rm -Rf *

10. Untar the mysql.tar into /var/lib on the Slave

cp /tmp/mysql.tar /var/lib/.
cd /var/lib
tar -xvf mysql.tar

NOTE: if you are using 5.6 and later remove the file auto.cnf in /var/lib/mysql/ to avoid errno 1593

Errno: 1593
Fatal error: The slave I/O thread stops because master and slave
have equal MySQL server UUIDs; these UUIDs must be different for
replication to work.

11. Change the /etc/my.cnf on the slave

under [mysqld]

slave-skip-errors=1062 # skip duplicate transaction errors
log-bin=/var/lib/mysql/log-bin # log bin location
relay_log=/var/lib/mysql/mysql_relay-bin # Relay log location
log_slave_updates=1
server-id=101 # we usually put the last digits of the slave IP
skip-slave-start # Does not restart replication on instance restart

I recommend that once the Slave/Replica will be created and sync with the Master that you remove the slave-skip-errors=1062 from the my.cnf file then that you restart the Slave/Replica and you restart the replication.

12. Start MySQL on the Slave

service mysql start

13. Configure the replication on the slave

Note: Remember the bin log file name and position from step 3

CHANGE MASTER TO
    MASTER_HOST='192.168.10.1', 
    MASTER_USER='repl',
    MASTER_PASSWORD='yourpassword',
    MASTER_LOG_FILE='binlognamefrompoint3',
    MASTER_LOG_POS=positionfrompoint3;

14. Start the replication on the slave

start slave;

15. Validate the replication

In the result of this command the most important line is Seconds_Behind_Master.
If the value in the line is NULL, this means that your replication with the Master is not working.
If the value is greater than 0, this means that your replication is catching on. if you run this command few time, the value should go down.
If the value is equal to 0, that means that your replication is running and up to date.

show slave status\G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log-bin.000056
          Read_Master_Log_Pos: 982311504
               Relay_Log_File: mysql_relay-bin.000130
                Relay_Log_Pos: 60222517
        Relay_Master_Log_File: log-bin.000056
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 982311504
              Relay_Log_Space: 60406973
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 123

Create a Slave/Replica using a mysqldump backup (No down time)

This option is almost identical to the first one except this time you are using a mysqldump backup and there will be no down time on the master.

1. Create a replication account on the Master

create user repl@'192.168.10.2' IDENTIFIED BY 'yourpassword';
GRANT REPLICATION SLAVE, REPLICATION CLIENT 
        ON *.*
        TO repl@'192.168.10.2' [ IDENTIFIED BY 'yourpassword' ];

2. Modify the /etc/my.cnf file on the Master

under [mysqld] 

expire_logs_days=2 #  purge bin logs older than 2 days
log-bin=/var/lib/mysql/log-bin # bin location (use full path)
server-id=100 # unique id for the master (default is 1)
sync_binlog=1 # to sync the binlogs

if you are using InnoDB it is recommended to use

innodb-flush-log-at-trx-commit=1 # This will flush the log write
innodn-support-xa=1 # mysql 5.5 and later

3. Take note of the Master current bin log and position

This information will be needed when setting the Slave/Replica later one.

show master status\G

*************************** 1. row ***************************
            File: log-bin.000056
        Position: 425307404
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.01 sec)

4. Restart your Master to enable the new parameters

You need to restart the Master in order to take the new parameters from themy.cnf.

service mysql restart

5. Take a backup of the Master using mysqldump

mysqldump -uroot --password=your_password --all-databases  --add-drop-database --flush-logs --master-data=2  > /tmp/mysql_backup.sql

gzip mysql_backup.sql

6. Restart the master

service mysql start

7. Transfer the backup to the Slave/Replica host using SCP

cd /tmp
scp mysql_backup.gz root@192.168.10.2:/tpm/.

8. Unzip the mysql_backup.sql.gz

cd /tmp

gunzip mysql_backup.sql.gz

9. Change the /etc/my.cnf on the slave

under [mysqld]

slave-skip-errors=1062 # skip duplicate transaction errors
log-bin=/var/lib/mysql/log-bin # log bin location
relay_log=/var/lib/mysql/mysql_relay-bin # Relay log location
log_slave_updates=1
server-id=101 # we usually put the last digits of the slave IP
skip-slave-start # Does not restart replication on instance restart

10. Start MySQL on the Slave

service mysql start

11. Restore the mysqldump backup into the Slave/Replica environment

this restore will overwrite all the databases.

cd /tmp

mysql -uroot -pyour_password < mysql_backup.sql

12. restart the Slave/Replica instance

service mysql restart

13. Configure the replication on the slave

Note: Remember the bin log file name and position from step 3

CHANGE MASTER TO
    MASTER_HOST='192.168.10.1', 
    MASTER_USER='repl',
    MASTER_PASSWORD='yourpassword',
    MASTER_LOG_FILE='binlognamefrompoint3',
    MASTER_LOG_POS=positionfrompoint3;

14. Start the replication on the slave

start slave;

15. Validate the replication

show slave status\G

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: log-bin.000056
          Read_Master_Log_Pos: 982311504
               Relay_Log_File: mysql_relay-bin.000130
                Relay_Log_Pos: 60222517
        Relay_Master_Log_File: log-bin.000056
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 982311504
              Relay_Log_Space: 60406973
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 123