MYSQL Administrator

yum install mysql-server

refer:

• http://dev.mysql.com/doc/refman/5.6/en/installing-source-distribution.html
• http://dev.mysql.com/doc/refman/5.7/en/installing-source-distribution.html

Download source: Plsease select Generic Linux (Architecture Independent), Compressed TAR Archive package for download

• newest source: https://dev.mysql.com/downloads/mysql/
• version 5.6: https://dev.mysql.com/downloads/mysql/5.6.html#downloads

Below are steps to install MySQL Server 5.6.35:

1. Step1: Add mysql user:

   useradd -r -U mysql -M -d /onec/mysql/data

2. Step2: Install depedency packages:

   yum install gcc-c++
   yum install cmake
   yum install bison
   yum install ncurses-devel

3. Step3: Generate Makefile

   rm -f CMakeCache.txt
   make clean
   cmake . -DCMAKE_INSTALL_PREFIX=/onec/mysql -DMYSQL_DATADIR=/onec/mysql/data 

4. Step4: Compile and Install

   make
   make install

5. Step5:chown to mysql:

   chown -R mysql.mysql /onec/mysql

6. Step6: copy my.cnf to /etc/my.cnf
7. Step7: Init database

   cd /onec/mysql
   scripts/mysql_install_db --datadir=/onec/mysql/data --user=mysql

8. Step8: Start Server:

   bin/mysqld_safe --user=mysql &

   or start with scripts:

   # Next command is optional
   cp support-files/mysql.server /etc/init.d/mysql.server
   /etc/init.d/mysql.server start

9. Step9: Login to Mysql server

   mysql -uroot

10. Step10: Reset password root

    mysqladmin password 'newpassword'

refer: http://dev.mysql.com/doc/refman/5.7/en/windows-install-archive.html

Users who are installing from the noinstall package can use the instructions in this section to manually install MySQL. The process for installing MySQL from a Zip archive is as follows:

1. Step1: Extract the main archive to the desired install directory. For example: Extract to directory d:\tools\mysql-5.7.13-winx64(Optional: also extract the debug-test archive if you plan to execute the MySQL benchmark and test suite)
2. Step2: Create an option file: When the MySQL server starts on Windows, it looks for option files in several locations, such as the Windows directory, C:\, and the MySQL installation directory. MySQL looks for options in each location first in the my.ini file, and then in the my.cnf file. And in this step, we create the my.ini copy from my-default.ini with custom information:

   [mysqld]
   # set basedir to your installation path
   basedir=d:\\tools\\mysql-5.7.13-winx64
   # set datadir to the location of your data directory
   datadir=d:\\tools\\mysql-5.7.13-winx64\data

3. Step3: Choose a MySQL server type
4. Step4: Initialize MySQL: To initialize the data directory, invoke mysqld with the –initialize(secure by default: including generation of a random initial root password) or –initialize-insecure in Installation Directory of MySQL server

   bin\mysqld --initialize
   bin\mysqld --initialize-insecure

   output: Init database mysql in data directory d:\\tools\\mysql-5.7.13-winx64\data. And with option –initialize, we check log .err in data directory to see temporary password for root:

   A temporary password is generated for root@localhost: 8mfak!jsyltB

   With old version:

   ./scripts/mysql_install_db --user=mysql
   ./bin/mysql_secure_installation -> set password for mysql

5. Step5: Start the MySQL server:

   bin\mysqld.exe --console

6. Step6: Install MySQL as service:

   d:\tools\mysql-5.7.13-winx64\bin\mysqld.exe --install

7. Step7: reset password root:

   ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

Check configuration file in linux:

cat /etc/init.d/mysqld | grep cnf

⇒ output:

# config: /etc/my.cnf

Check configuration file in windows:

• Go to services.msc to get property of service MySQLD
• View the Path To Execute: C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe“ –defaults-file=“C:\ProgramData\MySQL\MySQL Server 5.6\my.ini” MySQL56 ⇒ configuration file is C:\ProgramData\MySQL\MySQL Server 5.6\my.ini

/etc/init.d/mysqld start
/etc/init.d/mysqld stop
mysqladmin -u username –p shutdown

start:

/onec/mysql/bin/mysqld_safe --datadir="/onec/mysql/data" --pid-file="/onec/mysql/tmp/mysql.pid"

mysqladmin -p drop database_name
mysqladmin -p create database_name

• You only use MySQL client to connect MySQL Server to see the version:

  mysql -uroot -p
  Enter password:

  output:

  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 2
  Server version: 5.5.28 Source distribution

• Or use SQL Query to see the version informations:

  SHOW VARIABLES LIKE "%version%";

  output:

  +-------------------------+---------------------+
  | Variable_name           | Value               |
  +-------------------------+---------------------+
  | innodb_version          | 1.1.8               |
  | protocol_version        | 10                  |
  | slave_type_conversions  |                     |
  | version                 | 5.5.28              |
  | version_comment         | Source distribution |
  | version_compile_machine | x86_64              |
  | version_compile_os      | Linux               |
  +-------------------------+---------------------+

• Or use command STATUS:

  STATUS;

  output:

  mysql  Ver 14.14 Distrib 5.5.28, for Linux (x86_64) using  EditLine wrapper
  
  Connection id:          2
  Current database:
  Current user:           root@localhost
  SSL:                    Not in use
  Current pager:          stdout
  Using outfile:          ''
  Using delimiter:        ;
  Server version:         5.5.28 Source distribution
  Protocol version:       10
  Connection:             Localhost via UNIX socket
  Server characterset:    latin1
  Db     characterset:    latin1
  Client characterset:    utf8
  Conn.  characterset:    utf8
  UNIX socket:            /onec/mysql/tmp/mysql.sock
  Uptime:                 28 min 53 sec
  
  Threads: 1  Questions: 14  Slow queries: 0  Opens: 33  Flush tables: 1  Open tables: 26  Queries per second avg: 0.008

mysqlshow -u root -p

⇒ output

+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+

mysqladmin password 'newpassword'

mysqlshow -u root -p mysql

⇒output:

+---------------------------+
|          Tables           |
+---------------------------+
| columns_priv              |
| db                        |
| func                      |
| help_category             |
| help_keyword              |
| help_relation             |
.............................

• show all tables in database:

  SHOW TABLES;

• show all procedures in database:

  SHOW PROCEDURE STATUS WHERE Db='pp_ws_gunviet';
  SELECT Db,name,definer FROM mysql.proc WHERE  Db='pp_ws_gunviet';

• change definer of procedures

  UPDATE mysql.proc SET definer='root@localhost' WHERE Db='pp_ws_gunviet';

• Design of table

  DESCRIBE tablename;

• show procedure

  SHOW CREATE PROCEDURE `sp_gold_update_transaction`;

• change definer to root@localhost before import sql from sql dump file

commands:

root# mysqladmin -p create employee
root# mysql -p employee < create_database.sql
root# mysql -p employee < employee_data.sql

• show all users:

  select User,Host FROM mysql.user;

• show privillege of users:

  show grants for onec@localhost;

GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY [PASSWORD] 'password']
		[, user_name [IDENTIFIED BY 'password'] ...]
[REQUIRE
		NONE |
		[{SSL| X509}]
		[CIPHER cipher [AND]]
		[ISSUER issuer [AND]]
		[SUBJECT subject]]
[WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR # |
											MAX_UPDATES_PER_HOUR # |
											MAX_CONNECTIONS_PER_HOUR #]]

The ON clause specifies the items we are granting privileges on. This can be a named table or a named database with all its tables (dbname.*). We can also specify *.*, which means all databases and all tables. If we specify *, the privileges are granted on the currently selected database.

• Create user accounts and give users access to databases, tables, and functions:

  GRANT usage ON * TO luke@localhost IDENTIFIED BY 'password';

• Grant for user all privileges:

  GRANT ALL privileges ON dbname.* TO 'anhvc'@'localhost'  IDENTIFIED BY 'xxxxx';

• Grant for user at any host:

  GRANT ALL privileges ON *.* TO 'root'@'%'  IDENTIFIED BY 'xxxxx';

SET password FOR fred@localhost = password('newpassword');

We use command “DROP USER” ⇒ The statement removes privilege rows for the account from all grant tables.

• Syntax

DROP USER USER;

• Example

  DROP USER 'fred'@'localhost';

  And delete all username with name and not host

  DROP USER 'fred'@'%';

SHOW grants FOR fred@localhost;

The privileges we can grant using the GRANT statement can be divided into two basic categories:

• CREATE: User can create tables.
• CREATE TEMPORARY TABLES:User can create temporary tables.
• DELETE:User can delete rows.
• EXECUTE:User can execute procedures.
• INDEX: User can create indexes.
• INSERT:User can insert rows.
• LOCK TABLES:User can lock tables.
• SELECT:User can select rows.
• SHOW DATABASES:User can execute a SHOW DATABASES command to retrieve the list of available databases.
• UPDATE:User can update rows.
• USAGE:User can log in, but cannot do anything else.

• ALL:User has all the privileges except WITH GRANT OPTION.
• ALTER:User can alter tables. You may give this to some power users, but proceed with caution because it may be used to change the privilege tables.
• DROP:User can drop tables. You may give this to trusted users.
• FILE:User can load data from a file. Again, you may give this to trusted users. Beware of users trying to load arbitrary files, such as /etc/passwd or similar files!
• PROCESS:User can show full process list—that is, see all the processes that MySQL is executing.
• RELOAD:User can use the FLUSH statement. This has various purposes. We will look at FLUSH PRIVILEGES later in this chapter and will revisit FLUSH in Chapter 13.
• REPLICATION CLIENT:User can check where the masters and slaves are.
• REPLICATION SLAVE:Special privilege designed for the special replication user on the slave. See Chapter 16 for more details.
• SHUTDOWN:User can run mysqladmin shutdown. For more information see Chapter 13.
• SUPER: User can connect even if MySQL has its maximum number of connections and can execute the commands CHANGE MASTER, KILL (thread), mysqladmin debug, PURGE MASTER LOGS, and SET GLOBAL.
• WITH GRANT OPTION:User can pass on any privileges he has.

Some useful features are turned off by default, and it starts out insecure

• Using InnoDB tables, so we need to perform a basic setup for these tables.
• Turn on binary logging in all situations. This is highly useful for disaster recovery

  log-bin=filename

• Turn on slow query logging. This tracks slow queries (as you might expect from the name) and will help you optimize your applications

  log-slow-queries=filename

  And you must create the file filename with owner is mysql:

  touch filename
  chown mysql.mysql filename

• Configuration file /etc/my.cnf

  datadir=/var/lib/mysql
  log-error=/var/log/mysqld.log

• To get information about the server and how it's running, we can look at the server status and the values of variables
• The values of most of these variables can be set in your configuration file, from the command line when you start the server, or dynamically inside MySQL using the SET command.

• Using query

  mysql> SHOW STATUS;
  mysql> SHOW VARIABLES;

• Using mysqladmin

  # mysqladmin -u root -p extended-status
  # mysqladmin -u root -p variables

  ⇒ output:

* query variable name

SHOW VARIABLES WHERE variable_name='Port';

Some other particularly interesting values to look at are listed here:

• threads_connected: This is the current number of connections to the server.
• slow_queries: This is the number of queries this server has run that have taken more time than the value of the server variable long_query_time. These queries are logged in the Slow Query Log. We will return to slow queries in Chapter 19, “Optimizing Your Queries.”
• uptime: This is how long this server instance has been running in seconds.

The set statement is used to set the value of server variables—the same variables we could see via the show variables statement :

SET variable=VALUE;

For example, we might use:

SET sql_safe_updates=1;

You can see what processes are currently running on your server by running the following command inside MySQL:

• Using query

  SHOW processlist;

• Using mysqladmin

  # mysqladmin -u root -p processlist

  ⇒ output:

  +----+------+-----------+----+---------+------+-------+------------------+
  | Id | User | Host      | db | Command | Time | State | Info             |
  +----+------+-----------+----+---------+------+-------+------------------+
  | 73 | root | localhost |    | Query   | 0    |       | show processlist |
  +----+------+-----------+----+---------+------+-------+------------------+

If we have a problematic thread (for example, a query that is taking forever, or a problem user), we can terminate the thread using query:

mysqladmin KILL process_id

MySQL has a set of internal caches. These can be cleared using the flush and reset commands.

• clear the query cache:

  FLUSH query cache;

  ⇒ This will defragment the query cache, improving performance.

• clear the query cache:

  reset query cache;

  ⇒ Rather than defragmenting the query cache, this will actually clear it altogether.

MySQL keeps various log files that may be useful to you. Most of these logs are not enabled by default, so if you want logging, you will have to switch it on.
These are the logs you can keep:

• Error log: Tracks all the errors that have occurred. This one is logged by default and will appear in your data directory. The file is called hostname.err on Linux and mysql.err on Windows. You can set the location to something else with the option:

  [mysqld]
  .......
  log-error=filename

• Query log: Logs all the queries run on the system. You can turn on this log and specify the location with the option:

  [mysqld]
  ......
  log=filename

• Binary log: Logs all the queries that change data. This replaces the update log, which will still be around until MySQL version 5.0, but is deprecated. You can turn on this log and specify the location with the option:

  log-bin=filename

• Slow query log: Logs all queries that took longer to execute than the value stored in the variable long_query_time. You can turn on this log and specify the location with the option:

  [mysqld]
  ......
  log-slow-queries=filename

The binary log can be viewed using:

mysqlbinlog logfile

• [windows] (You must go to services.msc and select service MySQL Server to see the path of my.ini)

  [mysqld]
  .......
  log = "C:/genquery.log"

  And in MySQL 5.5 above, you turn on query log and change path of query log:

  general-log=1
  general_log_file="C:/MySQL-ANHVC-PC.log"

  ⇒ You must self create file C:/MySQL-ANHVC-PC.log for writing

• [linux] Show query log:
  1. ON log in mysql query:

     SET GLOBAL general_log = 'ON';

  2. to OFF log, we set query:

     SET GLOBAL general_log = 'OFF';

  3. Show query log config in mysql server:

     SHOW VARIABLES LIKE '%general%';

     output:

     +------------------+--------------------------------+
     | Variable_name    | Value                          |
     +------------------+--------------------------------+
     | general_log      | ON                             |
     | general_log_file | /onec/mysql/data/vdclinux6.log |
     +------------------+--------------------------------+

• Change default general log file:
  1. Edit my.cnf

     [mysqld]
     ........
     log=/var/log/mysqld.general.log

  2. Create basic file log:

     touch /var/log/mysqld.general.log
     chown mysql.mysql /var/log/mysqld.general.log

  3. Restart mysql:

     /etc/init.d/mysqld restart

1. Step1: Show slow log config in Mysql Server

   SHOW VARIABLES LIKE '%slow%';

   output:

   +---------------------+-------------------------------------+
   | Variable_name       | Value                               |
   +---------------------+-------------------------------------+
   | log_slow_queries    | OFF                                 |
   | slow_launch_time    | 2                                   |
   | slow_query_log      | OFF                                 |
   | slow_query_log_file | /onec/mysql/data/vdclinux6-slow.log |
   +---------------------+-------------------------------------+

2. Step2: Set config to enable slow log:

   SET GLOBAL slow_query_log = 'ON';

When we install mysql-server with yum in CentOS System, the default config .cnf will be installed to the directory /usr/share/mysql/

ls /usr/share/mysql/my
my-huge.cnf                     my-medium.cnf                   mysql_system_tables_data.sql
my-innodb-heavy-4G.cnf          my-small.cnf                    mysql_system_tables.sql
my-large.cnf                    mysql_fix_privilege_tables.sql  mysql_test_data_timezone.sql

Custom max connections:

max_connections = 1024
max_user_connections = 1024
query_cache_size=8M
join_buffer_size=1M
thread_cache_size=16
table_open_cache=400

Before run script check, we need to run automatic all pages of websites for running all queries in mysql

https://dev.mysql.com/doc/mysql-reslimits-excerpt/5.7/en/column-count-limit.html

Download script from https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl and run:

chmod +x mysqltuner.pl
./mysqltuner.pl

Output:

[OK] Currently running supported MySQL version 5.1.73
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +CSV +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 17M (Tables: 138)
[--] Data in InnoDB tables: 58M (Tables: 402)
[--] Data in MEMORY tables: 0B (Tables: 17)
[!!] Total fragmented tables: 417

-------- Security Recommendations  -------------------------------------------
[!!] User '@baby' has no password set.
[!!] User '@localhost' has no password set.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 40m 54s (11K q [4.487 qps], 257 conn, TX: 16M, RX: 3M)
[--] Reads / Writes: 87% / 13%
[--] Total buffers: 34.0M global + 2.7M per thread (151 max threads)
[!!] Maximum possible memory usage: 449.2M (91% of installed RAM)
[OK] Slow queries: 0% (0/11K)
[OK] Highest usage of available connections: 2% (4/151)
[!!] Key buffer size / total MyISAM indexes: 8.0M/29.3M
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
[!!] Temporary tables created on disk: 27% (247 on disk / 898 total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 17% (64 open / 375 opened)
[OK] Open file limit used: 0% (0/1K)
[OK] Table locks acquired immediately: 100% (13K immediate / 13K locks)
[!!] InnoDB  buffer pool / data size: 8.0M/59.0M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    key_buffer_size (> 29.3M)
    query_cache_size (>= 8M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_open_cache (> 64)
    innodb_buffer_pool_size (>= 58M)

Some information which you need to tuner:

-------- Storage Engine Statistics -------------------------------------------
[!!] Total fragmented tables: 417
-------- Security Recommendations  -------------------------------------------
[!!] User '@baby' has no password set.
[!!] User '@localhost' has no password set.
-------- Performance Metrics -------------------------------------------------
[!!] Maximum possible memory usage: 449.2M (91% of installed RAM)
[!!] Key buffer size / total MyISAM indexes: 8.0M/29.3M
[!!] Query cache is disabled
[!!] Temporary tables created on disk: 27% (307 on disk / 1K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 2% (64 open / 2K opened)
[!!] InnoDB  buffer pool / data size: 8.0M/59.0M

General recommendations:

1. Run OPTIMIZE TABLE to defragment tables for better performance:
   • Check Information:

     [!!] Total fragmented tables: 417

   • Tunning
2. Reduce your overall MySQL memory footprint for system stability
3. Set thread_cache_size to 4 as a starting value
   • Check Information:

     [!!] Thread cache is disabled

   • Tunning:

Variables to adjust:

1. MySQL's maximum memory usage is dangerously high:
   • Check Information

     [!!] Maximum possible memory usage: 449.2M (91% of installed RAM)

   • Tunning
2. key_buffer_size (> 29.3M)
   • Check Information

     [!!] Key buffer size / total MyISAM indexes: 8.0M/29.3M

   • Tunning in /etc/my.cnf:

     [mysqld]
     .............
     key_buffer_size=30M

3. query_cache_size (>= 8M)
   • Check Information

     [!!] Query cache is disabled

   • Tunning in /etc/my.cnf

     [mysqld]
     .............
     query_cache_size=8M
     query_cache_limit=1M

4. query_cache_limit (> 1M, or use smaller result sets)
   • Check Information

     [!!] Query cache efficiency: 0.0% (0 cached / 18 selects)
5. tmp_table_size (> 16M)
   • Check Information

     [!!] Temporary tables created on disk: 27% (323 on disk / 1K total)

   • Tunning with /etc/my.cnf

     tmp_table_size=32M

6. max_heap_table_size (> 16M)
   • Check Information

     max_heap_table_size=32M

   • Tunning
7. thread_cache_size (start at 4)
   • Check Information

     [!!] Thread cache is disabled

   • Tunning
8. table_open_cache (> 64)
   • Check Information

     [!!] Table cache hit rate: 3% (64 open / 1K opened)

   • Tunning in /etc/my.cnf

     [mysqld]
     .............
     table_open_cache=100

9. innodb_buffer_pool_size (>= 58M)
   • Check Information

     [!!] InnoDB  buffer pool / data size: 8.0M/59.0M

   • Tunning

refer:

• http://www.epigroove.com/blog/optimize-mysql-the-thread-cache
• http://www.dbasquare.com/kb/mysql-and-thread-cache-size/

MySQL uses a separate thread for each client connection. In environments where applications do not attach to a database instance persistently, but rather create and close a lot of connections every second, the process of spawning new threads at high rate may start consuming significant CPU resources. To alleviate this negative effect, MySQL implements thread cache, which allows it to save threads from connections that are being closed and reuse them for new connections. The parameter thread_cache_size defines how many unused threads can be kept alive at any time.

refer:

• https://mariadb.com/kb/en/mariadb/documentation/optimization-and-tuning/system-variables/optimizing-table_open_cache/
• http://www.percona.com/blog/2009/11/16/table_cache-negative-scalability/

table_open_cache can be a useful variable to adjust to improve performance.

Each concurrent session accessing the same table does so independently. This improves performance, although it comes at a cost of extra memory usage.

table_open_cache indicates the maximum number of tables the server keeps open. Ideally, you'd like this cached so as to re-open a table as infrequently as possible.

However, note that this is not a hard limit. When the server needs to open a table, it evicts the least recently used closed table from the cache, and adds the new table. If all tables are used, the server adds the new table and does not evict any table. As soon as a table is not used anymore, it will be evicted from the list even if no table needs to be open, until the number of open tables will be equal to table_open_cache.

You can view the current setting in the my.cnf file, or by running:

SELECT @@table_open_cache;

output:

To evaluate whether you could do with a higher table_open_cache, look at the number of opened tables, in conjunction with the server uptime (Opened_tables and Uptime status variables):

SHOW global STATUS LIKE 'opened_tables';

output:

If the number of opened tables is increasing rapidly, you should look at increasing the table_open_cache value. Try to find a value that sees a slow, or possibly even no, increase in the number of opened tables.

The open table cache can be emptied with FLUSH TABLES or with the flush-tables or refresh mysqladmin commands.

In some cases, MySQL creates internal temporary tables while processing queries. On the base of the dimensions of the resultset MySQL will use the MEMORY engine AND/OR the MyISAM engine. The difference is that MEMORY engine will handle the table in memory, while MyISAM will write it on disk. A table created using the MEMORY engine can be automatically converted by the MySQL server if it exceed the defined threshold

The tables explicitly created with CREATE TABLE ENGINE MEMORY use ONLY the max_heap_table_size system variable to determines how large the table is permitted to grow and there is no conversion to on-disk format.

Now let see what the manual say about how this two variables and their relation:

• The maximum size of internal in-memory temporary tables. (The actual limit is determined as the minimum of tmp_table_size and max_heap_table_size.)
• If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table.
• Increase the value of tmp_table_size (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory. This variable does not apply to user-created MEMORY tables.

key_buffer_size=30M
query_cache_size=8M
query_cache_limit=1M
join_buffer_size=512K
thread_cache_size=16
table_open_cache=400

And for connections to mysql server

max_connections = 1024
max_user_connections = 1024

Bear in mind that although backups are vitally important, all backups involve restricting access to the user while backups are being made
One solution to this issue is replication. You can take down one slave and back it up while users continue blissfully about their business. There are four ways you can make a backup in MySQL:

• Use the mysqldump script
• Use the mysqlhotcopy script
• Directly back up the data files yourself: This is really doing what mysqlhotcopy does, but manually. If you choose to use this option, you will need to either shut down the database or flush and lock all tables before copying to make sure that they are internally consistent. Both mysqldump and mysqlhotcopy will flush and lock for you, so they are easier, safer options.
• Use the BACKUP TABLE and RESTORE TABLE commands to back up or restore a specified table or set of tables.

• Backup(dump database): Create a dump file(a file containing the SQL statements necessary to re-create the database)

  mysqldump --opt -u username -p databasename > backup.sql

  example:

  mysqldump --opt -u root -p  mysql > backup.sql		

• Backup(dump table):

  mysqldump --opt -u username -p databasename tablename > backup_table.sql

• Backup Options:
  • Dump single transaction, routines(functions and procedures), triggers from the dumped databases:

    --single-transaction --routines --triggers

  • Backup(only table structure):

    --add-drop-database --no-data

  • Backup with fields in sql insert command

    --complete-insert

  • Backup with option skip comments and multiple sql insert command ⇒ For debugging changes in data rows of database

    --skip-comments --skip-extended-insert

• Restore: We could reload or re-create the databasename database elsewhere by doing the following:
  • step1: Creating an appropriately named database on the target machine

    mysqladmin -u username -p create backup_mysql

  • step2: Loading this file using:

    mysql  -f --default-character-set=utf8 --routines -u username -p backup_mysql < backup.sql

create a data file(This copies the data files for a particular database directly)

• dump

  mysqldump -u'root' -p dbname > dbname.sql

  Or dump with single transaction, routines, triggers

  mysqldump  -hlocalhost -uroot -p --add-drop-table --quote-names --opt --single-transaction --routines --triggers --add-locks --extended-insert --quick --compress dbname > dbname.sql

• restore

  mysql -f --default-character-set=utf8 -u'root' -p dbname < dbname.sql

mysqldump --databases passport --routines --no-data -uroot -p
mysqldump --all-databases --routines --no-data -uroot -p
mysqldump --databases `cat alldb.txt` --routines --no-data -uroot -p > alldatabases.sql

• dump all databases

mysqldump  -hlocalhost -u$USERNAME -p$PASSWORD -A --add-drop-table --quote-names --opt --routines --add-locks --extended-insert --quick --compress > dbname.sql

• dump with list databases

  mysqldump  -hlocalhost -u$USERNAME -p$PASSWORD --databases `cat alldb.txt` --add-drop-table --quote-names --opt --routines --add-locks --extended-insert --quick --compress > dbname.sql

• dump with list databases

  for i in `cat alldb.txt `; do mysqldump  -hlocalhost -uroot -pxxxx --add-drop-table --quote-names --opt --routines --add-locks --extended-insert --quick --compress $i> $i.sql; done

mysql -u$USERNAME -p$PASSWORD  <<< "DROP DATABASE IF EXISTS \`$DB\`;"
mysql -u$USERNAME -p$PASSWORD <<< "CREATE DATABASE \`$DB\`;"
mysql -f --default-character-set=utf8 -u$USERNAME -p$PASSWORD $DB < dbname.sql

LOAD DATA INFILE '/tmp/Itemsale-VNG.csv' INTO TABLE shopitemlist FIELDS TERMINATED BY ',';

(This method can cause error with Inno DB) Backup:

1. Step1: Stop mysqld

   /etc/init.d/mysqld stop

2. Step2: Copy data files to backup directory
3. Step3: Start mysql after backup finish:

   /etc/init.d/mysqld start

Restore:

1. Step1: Install mysql-server have the same version with above server
2. Step2: Copy backup data files to mysql data
3. Step2: start mysqld which use above data files

1. step1: backup users in mysql

    mysql -uroot -pxxxx -BNe "select concat('\'',user,'\'@\'',host,'\'') from mysql.user where user != 'root'" | while read uh; do mysql -uroot -pxxxx -BNe "show grants for $uh" | sed 's/$/;/; s/\\\\/\\/g'; done > grants.sql

2. step2: restore:

   mysql -uroot -p < grants.sql

• update define of column in table:

  ALTER TABLE `addmoney` MODIFY COLUMN `money`  INT(10) NOT NULL DEFAULT 0 AFTER `username`;

• add new column `chip` bigint(20) DEFAULT '0' into table

  ALTER TABLE `addmoney` ADD COLUMN `chip`  BIGINT(20) NOT NULL DEFAULT 0 AFTER `money`;

1. Step1: Dump old and new database with options below to remove comments and write all insert commands on multiple row:

   mysqldump --skip-comments --skip-extended-insert -uroot -p olddbname > olddbname.sql
   mysqldump --skip-comments --skip-extended-insert -uroot -p newdbname > newdbname.sql

2. Step2: Compare oldddname.sql and newdbname.sql to see changes

mysqldiff tools:

• Github:https://github.com/onishi/mysqldiff
• Download:

  git clone https://github.com/onishi/mysqldiff

• Data values are stored in machine format; this is fast, but machine/OS dependent
• Can't handle tables larger than 4GB
• data files: MyISAM table is stored on disk in three files:
  • .frm file stores the table definition
  • .MYD data file
  • .MYI index file
• config in [my.cnf]

  datadir=/db/mysql

• Transaction Model and Locking:In InnoDB, all user activity occurs inside a transaction.
  • If the autocommit mode is enabled: each SQL statement forms a single transaction on its own. MySQL always starts a new connection with autocommit enabled.
  • If the autocommit mode is switched off with SET AUTOCOMMIT = 0, then we can consider that:
    • a user always has a transaction open. An SQL COMMIT or ROLLBACK statement ends the current transaction and a new one starts
    • A COMMIT means that the changes made in the current transaction are made permanent and become visible to other users. A ROLLBACK statement, on the other hand, cancels all modifications made by the current transaction.
• Data files: InnoDB stores its tables and indexes in a tablespace, which may consist of several files (or raw disk partitions) ⇒ InnoDB tables can be of any size even on operating systems where file size is limited to 2GB.
• Config innodb:

  innodb_data_home_dir = /ibdata
  innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

• create table:

  CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=InnoDB;
  CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) TYPE=InnoDB;

1. Transaction-safe tables (TSTs) have several advantages over non-transaction-safe tables (NTSTs)
   • Safer. Even if MySQL crashes or you get hardware problems, you can get your data back, either by automatic recovery or from a backup plus the transaction log.
   • You can combine many statements and accept them all at the same time with the COMMIT statement (if autocommit is disabled). Within a transaction with autocommit disabled, changes to non-transaction-safe tables still are committed immediately and cannot be rolled back.
   • You can execute ROLLBACK to ignore your changes (if autocommit is disabled).
   • If an update fails, all your changes will be restored. (With non-transaction-safe tables, all changes that have taken place are permanent.)
   • Transaction-safe storage engines can provide better concurrency for tables that get many updates concurrently with reads
2. Examples from double-entry accounting systems often illustrate the concept of transactions. In double-entry accounting every debit requires the recording of an associated credit. If one writes a check for €100 to buy groceries, a transactional double-entry accounting system must record the following two entries to cover the single transaction:
   1. Debit €100 to Groceries Expense Account
   2. Credit €100 to Checking Account

A transactional system would make both entries — or both entries would fail. By treating the recording of multiple entries as an atomic transactional unit of work the system maintains the integrity of the data recorded. In other words, nobody ends up with a situation in which a debit is recorded but no associated credit is recorded, or vice versa.

That ibdata isn't shrinking is a particularly annoying feature of MySQL. The ibdata1 file can´t actually be shrunk unless you delete all databases, remove the files and reload a dump.

1. Step1: Do a mysqldump of all databases, procedures, triggers etc except the mysql and performance_schema databases
2. Step2: Drop all databases except the above 2 databases
3. Step3: Stop mysql
4. Step4: Delete ibdata1 and ib_log files
5. Step5: Start mysql
6. Step6: Restore from dump

1. Step1: access database mysql with user root

   /sbin/mysql.server stop
   /usr/local/mysql/bin/mysqld_safe --skip-grant-tables &
   mysql --user=root mysql

2. Step2: Update password of any user:

   UPDATE USER SET Password=PASSWORD('9ioue87$#90') WHERE USER='root';

• config in [/etc/mysql/my.cnf]

  log-bin = /var/log/mysql/mysql-bin.log
  binlog-do-db=exampledb
  server-id=1

  (config log-bin:these logs are used by the slave to see what has changed on the master

binlog-do-db=exampledb:replicate the database exampledb)

• Create a user with replication privileges:

  GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '<some_password>';

• flush tables witch read lock:

  USE exampledb;
  FLUSH TABLES WITH READ LOCK;
  SHOW MASTER STATUS;

• transfer database from master to slave: use mysqldump
• config replicate /etc/mysql/my.cnf:

  server-id=2
  master-host=192.168.0.100
  master-user=slave_user
  master-password=secret
  master-connect-retry=60
  replicate-do-db=exampledb
  master-port     =  3306

• connect to master

  SLAVE STOP;
  CHANGE MASTER TO MASTER_HOST='192.168.0.100',MASTER_PORT=3306, MASTER_USER='slave_user', MASTER_PASSWORD='<some_password>', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=183;

refer: https://dev.mysql.com/doc/refman/5.6/en/upgrading.html

1. Step1: Create file /etc/mylocal.cnf from /etc/my.cnf and edit it with content below:

   [mysqld]
   datadir=/data/mysql
   socket=/var/lib/mysqllocal/mysql.sock
   user=mysql
   # Disabling symbolic-links is recommended to prevent assorted security risks
   symbolic-links=0
   
   [mysqld_safe]
   log-error=/var/log/mysqldlocal.log
   pid-file=/var/run/mysqld/mysqldlocal.pid

2. Step2: Copy database from old mysql server to /data/mysql:

   cp -r /var/lib/mysql/* /data/mysql/
   chown -R mysql.mysql /data/mysql

3. Step3: Start other instance with command below:

   /usr/bin/mysqld_safe --defaults-file=/etc/mylocal.cnf --port=3307 &

kill `cat /var/run/mysqld/mysqldlocal.pid`

Add both option –port and –protocol(or –host)

mysql -uroot -p --port=3307 --protocol=tcp

A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set. Let's make the distinction clear with an example of an imaginary character set. For example, character set latin1 (``ISO-8859-1 West European'') has the following collations:

Collation 			Meaning
latin1_bin 			Binary according to latin1 encoding
latin1_danish_ci 	Danish/Norwegian
latin1_german1_ci 	German DIN-1
latin1_german2_ci 	German DIN-2
latin1_swedish_ci 	Swedish/Finnish
latin1_general_ci 	Multilingual

• Show character set:

  mysql> SHOW CHARACTER SET;

  output:

* Config in /usr/share/mysql/charsets/Index.xml:

<charset name="gbk">
  <family>East Asian</family>
  <description>GBK Simplified Chinese</description>
  <alias>cp936</alias>
  <collation name="gbk_chinese_ci"      id="28" order="Chinese">
    <flag>primary</flag>
    <flag>compiled</flag>
  </collation>
  <collation name="gbk_bin"     id="87" order="Binary">
    <flag>binary</flag>
    <flag>compiled</flag>
  </collation>
</charset>
 
<charset name="utf8">
  <family>Unicode</family>
  <description>UTF-8 Unicode</description>
  <alias>utf-8</alias>
  <collation name="utf8_general_ci"     id="33">
   <flag>primary</flag>
   <flag>compiled</flag>
  </collation>
  <collation name="utf8_bin"            id="83">
    <flag>binary</flag>
    <flag>compiled</flag>
  </collation>
</charset>

• Show collation:

  mysql> SHOW collation;

  output:

⇒ not install gbk_chinese_ci

• SHOW VARIABLES LIKE 'character_set_database':

Refer: http://ific.uv.es/informatica/manuales/MySQL-4.1.1/manual_Charset.html#Charset-defaults Install character set gbk:

./configure mysql with option: --with-extra-charsets=gbk

1. issues 1:
   • Run command:

     # mysqlshow

     ⇒ mysqlshow: Access denied for user 'root'@'localhost' (using password: NO)

   • Resolve: According to the error message, root@localhost has a password, but you aren't supplying it. Add -p to your commands to be prompted for the password.

     normal user: mysqlshow -u root -p
     root user: mysqlshow -p

2. issue 2:
   • Run command:

     telnet 192.168.191.128 3306

     ⇒ Host '192.168.191.1' is not allowed to connect to this MySQL server

   • Resolve:

     GRANT usage	ON * TO anhvc@192.168.191.128 IDENTIFIED BY 'password';
     GRANT ALL privileges ON *.* TO 'ztgame'@'10.30.31.%' IDENTIFIED BY 'ztgame';

3. issue3: the table CHARBASE is full when the size of data on disk harddriver is 4G
   • Get satus:

     SHOW TABLE STATUS;
     SHOW TABLE STATUS LIKE 'CHARBASE';

4. issue4: PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away: Edit my.ini

   [mysqld]
   max_allowed_packet = 16M

   ⇒ for import sql file. And

   [mysqldump]
   max_allowed_packet = 16M

   ⇒ for dump sql file