MYSQL - How to install MYSQL8 on Ubuntu

■ How to install MYSQL8 on Ubuntu

1, Download repository package of MYSQL.

2, Install the Repository package of MYSQL.
    $ sudo dpkg -i mysql-apt-config_0.8.16-1_all.deb

3, Select Mysql Server & Cluster (Currently selected: mysql-8.0)

4, Select Mysql-8.0

5, Update the information of the package and install mysql8
    $ sudo apt install mysql-server

Mysql - How to build replication server

■ In the master

mysql> GRANT REPLICATION SLAVE ON *.* TO ‘sampleRepl'@'172.0.0.%’ IDENTIFIED BY ‘password’;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      279 |              |                  |
+------------------+----------+--------------+------------------+

■ In the Slave

CHANGE MASTER TO MASTER_HOST='172.0.0.1', MASTER_USER='sampleRepl',
  MASTER_PASSWORD='password', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=279, MASTER_CONNECT_RETRY=10;

■■■ If It execute without MASTER_LOG_POS, It will start replication from first.■■■

■ Start the slave and check it.

 mysql>slave start
 mysql>SHOW SLAVE STATUS¥G

■ If you need to do a Revoke for stopping salve

REVOKE REPLICATION SLAVE ON *.* FROM 'sample_db'@'172.0.0.%’;

■ This is the my.cnf file

[client]
port            = 3306
socket        = /tmp/mysql.sock

# The MySQL server
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 1024M
max_allowed_packet = 1M
table_open_cache = 128
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
# Set server ip
server-id       = 11111

# Uncomment the following if you are using InnoDB tables
#skip-innodb
innodb_data_home_dir = /usr/local/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

#[mysqld_safe]
#pid-file=/usr/local/mysql/data/mysql-dbm01.pid

■ This is the command to make a user and give privileges.
GRANT ALL PRIVILEGES ON sample_db.* TO userId@localhost IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON sample_db.* TO userId@'172.0.0.%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

ShellScript - Kill process in mysql automatically

■ This is a shell script to kill process lacked.

#!/bin/bash

array_name=(`/usr/local/mysql/bin/mysql -N -e "SHOW FULL PROCESSLIST" -u root -p databasesName | grep userId | gawk -F"\t" '{print $1}' | tr '\n' ' '`);

for (( i = 0 ; i < ${#array_name[@]} ; i++ )) ; do
    echo "`/usr/local/mysql/bin/mysql -N -e "kill ${array_name[$i]}" -u root -p databasesName`";
    #echo "value[$i] = ${array_name[$i]}"
done

■ This is just memo - command
mysql>SHOW ENGINE INNODB STATUS;

Mysql - test the transaction step by step

■ Showing you which threads are running. ①

mysql> SHOW FULL PROCESSLIST;
+-----+------+-----------+------+---------+------+-------+-----------------------+
| Id  | User | Host      | db   | Command | Time | State | Info                  |
+-----+------+-----------+------+---------+------+-------+-----------------------+
|  32 | root | localhost | test | Sleep   |   20 |       | NULL                  |
| 161 | root | localhost | test | Sleep   |  961 |       | NULL                  |
| 246 | root | localhost | NULL | Query   |    0 | init  | SHOW FULL PROCESSLIST |
+-----+------+-----------+------+---------+------+-------+-----------------------+
3 rows in set (0.00 sec)

■ Check the process list⑥

mysql> SHOW FULL PROCESSLIST;
+-----+------+-----------+------+---------+------+-------+-----------------------+
| Id  | User | Host      | db   | Command | Time | State | Info                  |
+-----+------+-----------+------+---------+------+-------+-----------------------+
|  32 | root | localhost | test | Sleep   |   15 |       | NULL                  |
| 161 | root | localhost | test | Sleep   |    5 |       | NULL                  |
| 246 | root | localhost | NULL | Query   |    0 | init  | SHOW FULL PROCESSLIST |
+-----+------+-----------+------+---------+------+-------+-----------------------+
3 rows in set (0.00 sec)

■ Check the process list⑪

mysql> SHOW FULL PROCESSLIST;
+-----+------+-----------+------+---------+------+----------+-----------------------------------------+
| Id  | User | Host      | db   | Command | Time | State    | Info                                    |
+-----+------+-----------+------+---------+------+----------+-----------------------------------------+
|  32 | root | localhost | test | Sleep   |  531 |          | NULL                                    |
| 161 | root | localhost | test | Query   |    8 | updating | UPDATE table_one SET key_one='key_one3' |
| 246 | root | localhost | NULL | Query   |    0 | init     | SHOW FULL PROCESSLIST                   |
+-----+------+-----------+------+---------+------+----------+-----------------------------------------+
3 rows in set (0.00 sec)

■ Check the process list⑫

mysql> SHOW FULL PROCESSLIST;
+-----+------+-----------+------+---------+------+-------+-----------------------+
| Id  | User | Host      | db   | Command | Time | State | Info                  |
+-----+------+-----------+------+---------+------+-------+-----------------------+
|  32 | root | localhost | test | Sleep   |  616 |       | NULL                  |
| 161 | root | localhost | test | Sleep   |   93 |       | NULL                  |
| 246 | root | localhost | NULL | Query   |    0 | init  | SHOW FULL PROCESSLIST |
+-----+------+-----------+------+---------+------+-------+-----------------------+
3 rows in set (0.00 sec)

===============================This is A connection================================
■ Selected the data②

mysql> SELECT * FROM table_one;
+----+-------+----------+------------+
| id | name  | key_one  | value_one  |
+----+-------+----------+------------+
|  1 | name1 | key_one1 | value_one1 |
+----+-------+----------+------------+
1 row in set (0.00 sec)

■ Started TRANSACTION③

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

■ Selected the data④

mysql> SELECT * FROM table_one;
+----+-------+----------+------------+
| id | name  | key_one  | value_one  |
+----+-------+----------+------------+
|  1 | name1 | key_one1 | value_one1 |
+----+-------+----------+------------+
1 row in set (0.00 sec)

■ Updated the data⑤

mysql> UPDATE table_one SET key_one='key_one2';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

■ Selected the data for checking it⑦

mysql> SELECT * FROM table_one;
+----+-------+----------+------------+
| id | name  | key_one  | value_one  |
+----+-------+----------+------------+
|  1 | name1 | key_one2 | value_one1 |
+----+-------+----------+------------+
1 row in set (0.00 sec)

================================This is B connection===============================
■ Selected the data⑧

mysql> SELECT * FROM table_one;
+----+-------+----------+------------+
| id | name  | key_one  | value_one  |
+----+-------+----------+------------+
|  1 | name1 | key_one1 | value_one1 |
+----+-------+----------+------------+
1 row in set (0.00 sec)

■ Started TRANSACTION⑨

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

■ Selected the data for checking it⑩

mysql> SELECT * FROM table_one;
+----+-------+----------+------------+
| id | name  | key_one  | value_one  |
+----+-------+----------+------------+
|  1 | name1 | key_one1 | value_one1 |
+----+-------+----------+------------+
1 row in set (0.00 sec)

■ Showing the error message and rollback automatically after past the default 50 second.⑪

mysql> UPDATE table_one SET key_one='key_one3';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Mysql - useful Tips

@ Check the character set on mysql.
mysql> status

mysql> show variables like 'character%';

@ How to copy 1 column from one DB to another DB.
mysql> INSERT INTO `target_databases`.`target_table` (`column21`, `column22`, `column23`) 
SELECT `column11`, `column12`, 1 FROM `source_databases`.`source_table` WHERE column15='test';

@ When these are different DB, -- This is option (--skip-column-names, -N)
$ ./mysql -e "SELECT column1, column2, 1 FROM database_name.table_name WHERE column3 in ('test1', 'test2')" -u root -p xxxx database_name > ~/dumpFile.txt
mysql>LOAD DATA LOCAL INFILE '~/dumpFile.txt' INTO TABLE target_table_name;

@ Backup only the schema
$ mysqldump -u root -p --no-data database_name > ~/onlySchema.sql

@ Ignores foreign keys constraints (0 : ignore, 1:value)
mysql> SET FOREIGN_KEY_CHECKS=0

@ Backup databases
$ ./mysqldump -u root -p Database_Name > DB_backup.sql
$ ./mysql -u root -p Database_Name < DB_backup.sql

@ Create a databases
@ Check character set
mysql> show variables like 'character%';
mysql> CREATE DATABASE databases DEFAULT CHARACTER SET utf8 or utf8mb4;

@SELECT VARIABLES values
mysql>show VARIABLES where Variable_name like '%log%';
@ Start to print logs into a file in mysql
mysql>SET GLOBAL general_log='on';
@If you want to change a path of a log file, change FILE for a path you want
mysql>SET GLOBAL log_output = ‘FILE’;

@Modify a column property
mysql> ALTER TABLE apns_book MODIFY book_badge VARCHAR(2) NULL;

@Remove [on update CURRENT_TIMESTAMP]
# Set the same column name
mysql> ALTER TABLE table_name CHANGE column_name column_name TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

@ How to load data into table from CSV in Mac
mysql> LOAD DATA LOCAL INFILE '~/test.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r';

@ How to control data encrypted.
# select
mysql>SELECT AES_DECRYPT(column_name, 'password') as name FROM table_name;
# update
mysql>UPDATE table_name SET name=AES_ENCRYPT('Update Data', 'password') WHERE id=1;

@ Select data encrypted
mysql>SELECT distinct AES_DECRYPT(column_name, 'password') as test FROM table_name WHERE AES_DECRYPT(column_name, 'password') LIKE '%test%';

@ Change character set
mysql> SET character_set_client = utf8;
mysql> SET character_set_results = utf8;
mysql> SET character_set_connection = utf8;
mysql> ALTER DATABASE DB명 DEFAULT CHARACTER SET utf8;

@ Just Memo
semi  synchronize replication, sync_binlog sync_relay_log 

http://bryan7.tistory.com/101

Mysql - There is a good tool to arrange SQL easily

WWW - Wow

@ I was in trouble to have a complicated Sql.
@ But I searched for a good tool to help arranging Sql on Internet.
@ I found the following good tool.

http://www.dpriver.com/pp/sqlformat.htm

Mysql - how to alter a column in a table.

@ Add a column into a table in mysql

@ Varchar
ALTER TABLE table_name ADD new_column_name VARCHAR(45) NULL column_name_being;
@ Smallint or int
ALTER TABLE table_name ADD new_column_name SMALLINT UNSIGNED NULL DEFAULT '0' AFTER column_name_being;

Mysql - How to add user on the mysql

■ Make a db name before the following commands
mysql> GRANT ALL PRIVILEGES ON db_name.* TO user_name@localhost IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON db_name.* TO user_name@'127.0.%' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;

■ Make a new Mysql user
■ The % means the user 'selectUser' connecting from any host.
mysql>CREATE USER 'selectUser'@'%' IDENTIFIED BY 'password';
mysql>GRANT SELECT ON databaseName.* TO 'selectUser'@'%';

■ Make a new Mysql user
mysql>DROP USER 'sample'@'192.168.%';

■ Reference URL
http://www.alphadevx.com/a/388-Adding-a-read-only-MySQL-user

Mysql - Remove the bin log

@Remove the bin log
mysql -e "PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 60 DAY)"

Mysql - One sequence table can manage

It show you that the sequence table can manage all tables.

// These are Schema Desgins
CREATE TABLE zz_app
(
    id BIGINT UNSIGNED NOT NULL DEFAULT '0',
    app_id VARCHAR(45) NOT NULL,
    app_aaa VARCHAR(45) NULL,
    app_bbb VARCHAR(45) NULL,
    app_status_flag CHAR(1) NULL,
    insert_time TIMESTAMP NOT NULL,
    update_time TIMESTAMP NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE INDEX zz_app_idx1 ON zz_app(app_id);
CREATE INDEX zz_app_idx2 ON zz_app(insert_time);

CREATE TABLE zz_sequence
(
    seq_name VARCHAR(30) NOT NULL,
    id BIGINT UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (seq_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

----------------------------------------------------------------------

// Update sequence number into the Mysql
    <insert id="updateSequece" parameterType="map">
        UPDATE
            zz_sequence
        SET
            id=LAST_INSERT_ID(id+1)
        WHERE
            seq_name = #{seqName}
        <selectKey resultType="Long" order="AFTER">
            SELECT
                LAST_INSERT_ID()
        </selectKey>
    </insert>

// Insert a data into the Mysql
    <insert id="insertApp" parameterType="map">
        <selectKey keyProperty="id" resultType="Long" order="BEFORE">
            SELECT
                id
            FROM
                zz_sequence
            WHERE
                seq_name = #{seqName};
        </selectKey>
        INSERT INTO zz_app (
            id,
            app_id,
            app_aaa,
            app_bbb,
            app_status_flag,
            insert_time,
            update_time
        ) VALUES (
            #{id},
            #{appId},
            #{appAaa},
            #{appBbb},
            #{appStatusFlag},
            now(),
            now()
        )
    </insert>

Mysql - How to remove mysql on the mac

• sudo rm /usr/local/mysql
• sudo rm -rf /usr/local/mysql*
• sudo rm -rf /Library/StartupItems/MySQLCOM
• sudo rm -rf /Library/PreferencePanes/My*
• edit /etc/hostconfig and remove the line MYSQLCOM=-YES-
• sudo rm -rf /Library/Receipts/mysql*
• sudo rm -rf /Library/Receipts/MySQL*
• sudo rm -rf /var/db/receipts/com.mysql.*

Mysql - Install mysql5.5

cmake install - manual
--------------
./bootstrap
make
make install
--------------

mysql5.5 install
-------------
$ yum groupinstall "Development Tools"
$ yum install ncurses-devel
$ yum install cmake

$ cmake . \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DSYSCONFDIR=/etc \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DENABLED_LOCAL_INFILE=1 \
-DENABLED_PROFILING=1 \
-DMYSQL_TCP_PORT=3306 \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
-DWITH_DEBUG=1 \
-DWITH_EMBEDDED_SERVER=1;
$ make
$ make install

$ useradd mysql

$ cd /usr/local/src/mysql-5.5.38

$ chmod 755 scripts/mysql_install_db
$ scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

@ Start and Stop
$ cp /usr/local/mysql/support-files/mysql.server /etc/init.d/
@ Configuration
$ cp /usr/local/mysql/support-files/my-medium.cnf /etc/my.cnf

mac
http://hoyanet.pe.kr/1942