User Tools

Site Tools


mysqladmin

Table of Contents

MYSQL Administrator

Install Mysql Server

Install with Yum

yum install mysql-server

Install from binary source

Install from source

refer:

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

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'

Installing MySQL on Microsoft Windows Using a noinstall Zip Archive

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 [email protected]: 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';

MySQL Server Configuration

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

Basic commands

Stop, start mysql server with mysqld

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

Stop, start mysql server manual with mysqld_safe

start:

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

Drop, Create database with mysqladmin

mysqladmin -p drop database_name
mysqladmin -p create database_name

Show MySQL version

  • 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:           [email protected]
    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

List of databases and tables with mysqlshow

List of databases

mysqlshow -u root -p

⇒ output

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

change password of user

mysqladmin password 'newpassword'

get a list of the tables in that database

mysqlshow -u root -p mysql

⇒output:

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

Show tables and procedure information

  • 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='[email protected]' WHERE Db='pp_ws_gunviet';
  • Design of table
    DESCRIBE tablename;
  • show procedure
    SHOW CREATE PROCEDURE `sp_gold_update_transaction`;
  • change definer to [email protected] before import sql from sql dump file

Create database from script .sql

commands:

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

Managing User Privileges

show all users

  • show all users:
    select User,Host FROM mysql.user;
  • show privillege of users:
    show grants for [email protected];

Creating user accounts with GRANT and REVOKE

Syntax

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.

Example

  • Create user accounts and give users access to databases, tables, and functions:
    GRANT usage ON * TO [email protected] 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';

Change User's Password

SET password FOR [email protected] = password('newpassword');

Delete user

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

SHOW grants FOR [email protected];

Privilege Levels

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

user-level privileges

  • 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.

Administrator-level privileges

  • 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.

System Admin Mysql Server

Configuring Mysql Server

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

Server Status and Variables

  • 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.

Show status and variables

  • Using query
    mysql> SHOW STATUS;
    mysql> SHOW VARIABLES;
  • Using mysqladmin
    # mysqladmin -u root -p extended-status
    # mysqladmin -u root -p variables

    ⇒ output:

Variable_name Value
Aborted_clients 0
Aborted_connects 2
Binlog_cache_disk_use 0
Binlog_cache_use 0
Bytes_received 7366
Bytes_sent 43671

* 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.

Setting variables

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;

Viewing Process Information And Killing

View Process Information

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 |
    +----+------+-----------+----+---------+------+-------+------------------+

Killing Process or Thread

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

Clearing caches

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.

Understanding the log files

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

Query log of mysql server

  • [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

Show slow log of mysql server

  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';

MySQL Configuration

MySQL default configuration

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 MySQL Configuration

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

Tunning Mysql Server

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

MySQL table limit

Run script check

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

Tunning some basic parameters

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

Thread_cache_size

refer:

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.

table_open_cache

refer:

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:

@@table_open_cache
400

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:

Variable_name Value
Opened_tables 354858

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.

tmp_table_size and max_heap_table_size

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.

Final my.cnf for tunning

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

Backing Up and Restoring Your Database

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.

Use the mysqldump script and restore

  • 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

Use the mysqlhotcopy script

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

Examples for backup with dump

Dump single database

  • 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

Dump structure of database

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

  • 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

Import database

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 ',';

Backup and restore data files

(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

Backup and Restore users in mysql

  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

Modify Mysql database Design

Modify column of table

  • 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`;

Change Table Name

Compare changes in new database when compare with old database

  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

Using mysqldiff for comparing new database and old database

mysqldiff tools:

Storage Engine

MyISAM(non-transaction-safe tables)

  • 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

InnoDB(transaction-safe tables)

  • 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;

Compare Transaction-safe tables (TSTs) And non-transaction-safe tables (NTSTs)

  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.

How to shrink/purge ibdata1 file in MySQL

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

Reset password of user in MySql server

  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';

Replicate Config

config master

  • 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;

config slave

  • 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;

Upgrade MySQL Server

Start MySQL Server with multiple Instances on the same host

Start the Instance

  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 &

Stop the Instance

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

Connect to new MySql server

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

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

Character set and Collation

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

View charater set information in sql server

  • Show character set:
    mysql> SHOW CHARACTER SET;

    output:

Charset Description Default collation Maxlen
utf8 UTF-8 Unicode utf8_general_ci 3

* 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:

Collation Charset Id Default Compiled Sortlen
utf8_general_ci utf8 33 Yes Yes 1
utf8_bin utf8 83 Yes 1

⇒ not install gbk_chinese_ci

  • SHOW VARIABLES LIKE 'character_set_database':
Variable_name Value
character_set_database latin1

Install character set

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

Some issues

  1. issues 1:
    • Run command:
      # mysqlshow

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

    • Resolve: According to the error message, [email protected] 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 [email protected].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

mysqladmin.txt · Last modified: 2019/09/30 17:51 by admin