utilize mysqld_multi Start managing multiple instances

A script for the official management of multiple instances peer

# Empty the previous Directory

[root@mysql01 mysql]# tree /data/mysql/

/data/mysql/

├── mysql_3306

│   ├── data

│   ├── logs

│   ├── my3306.cnf

│   └── tmp

├── mysql_3307

│   ├── data

│   ├── logs

│   ├── my3307.cnf

│   └── tmp

└── yejr_3306

#3307 Directory planning

3306

/data/mysql/mysql_3306/{data,logs,tmp}

/data/mysql/mysql_3306/my3306.cnf

3307

/data/mysql/mysql_3307/{data,logs,tmp}

/data/mysql/mysql_3307/my3307.cnf

[root@mysql01 ~]# cat /etc/my.cnf

#my.cnf

[client]

port            = 3306

socket          = /tmp/mysql.sock

[mysql]

prompt="\\u@\\h [\\d]>"

#pager="less -i -n -S"

#tee=/opt/mysql/query.log

no-auto-rehash

[mysqld_multi]

mysqld = /usr/local/mysql/bin/mysqld_safe

mysqladmin = /usr/local/mysql/bin/mysqladmin

log = /opt/mysql/mysqld_multi.log

user = mdev

password = mdev4admin

[mysqld]

#misc

user = mysql

basedir = /usr/local/mysql

datadir = /data/mysql/mysql_3306/data

port = 3306

socket = /tmp/mysql.sock

event_scheduler = 0

tmpdir = /data/mysql/mysql_3306/tmp

#timeout

interactive_timeout = 300

wait_timeout = 300

#character set

character-set-server = utf8

open_files_limit = 65535

max_connections = 100

max_connect_errors = 100000

#lower_case_table_names =1

#logs

log-output=file

slow_query_log = 1

slow_query_log_file = slow.log

log-error = error.log

log_warnings = 2

pid-file = mysql.pid

long_query_time = 1

#log-slow-admin-statements = 1

#log-queries-not-using-indexes = 1

log-slow-slave-statements = 1

#binlog

#binlog_format = STATEMENT

binlog_format = row

server-id = 23306

log-bin = /data/mysql/mysql_3306/logs/mysql-bin

binlog_cache_size = 4M

max_binlog_size = 256M

max_binlog_cache_size = 1M

sync_binlog = 0

expire_logs_days = 10

#procedure

log_bin_trust_function_creators=1

#

gtid-mode = 0

#relay log

skip_slave_start = 1

max_relay_log_size = 128M

relay_log_purge = 1

relay_log_recovery = 1

relay-log=relay-bin

relay-log-index=relay-bin.index

log_slave_updates

#slave-skip-errors=1032,1053,1062

#skip-grant-tables

#buffers & cache

table_open_cache = 2048

table_definition_cache = 2048

table_open_cache = 2048

max_heap_table_size = 96M

sort_buffer_size = 128K

join_buffer_size = 128K

thread_cache_size = 200

query_cache_size = 0

query_cache_type = 0

query_cache_limit = 256K

query_cache_min_res_unit = 512

thread_stack = 192K

tmp_table_size = 96M

key_buffer_size = 8M

read_buffer_size = 2M

read_rnd_buffer_size = 16M

bulk_insert_buffer_size = 32M

#myisam

myisam_sort_buffer_size = 128M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

#innodb

innodb_buffer_pool_size = 100M

innodb_buffer_pool_instances = 1

innodb_data_file_path = ibdata1:100M:autoextend

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 8M

innodb_log_file_size = 100M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 50

innodb_file_per_table = 1

innodb_rollback_on_timeout

innodb_status_file = 1

innodb_io_capacity = 2000

transaction_isolation = READ-COMMITTED

innodb_flush_method = O_DIRECT

# The port number is 3306 Examples of special configuration

[mysqld3306]

port = 3306

datadir = /data/mysql/mysql_3306/data

tmpdir = /data/mysql/mysql_3306/tmp

log-bin = /data/mysql/mysql_3306/logs/mysql-bin

server-id = 1003306

socket = /tmp/mysql_3306.sock

# Reconfigure these options , Not like global configuration , Will directly override the global settings above

innodb_buffer_pool_size = 500M

[mysqld3307]

port = 3307

datadir = /data/mysql/mysql_3307/data

tmpdir = /data/mysql/mysql_3307/tmp

log-bin = /data/mysql/mysql_3307/logs/mysql-bin

server-id = 1003307

socket = /tmp/mysql_3307.sock

# Reconfigure these options , Not like global configuration , Will directly override the global settings above

innodb_buffer_pool_size = 500M

# Yes 3306、3307 To initialize

/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3306/my3306.cnf --initialize

/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3307/my3307.cnf --initialize

# Remember this initial password

cat /data/mysql/mysql_3306/data/error.log |grep password

2016-08-21T03:34:16.214559Z 1 [Note] A temporary password is generated for root@localhost: &%mh9C(jsbTF

cat /data/mysql/mysql_3307/data/error.log |grep password

2016-08-21T03:34:25.835638Z 1 [Note] A temporary password is generated for root@localhost: YMgbgJLkB9)r

# Use mysqld_multi Choose one of two ways to start , Can be started :

mysqld_multi start 3306

mysqld_multi start 3307

/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3306/my3306.cnf &

/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3306/my3306.cnf &

# Check for startup errors after startup

cat /data/mysql/mysql_3306/data/error.log

cat /data/mysql/mysql_3307/data/error.log

mysql -S /tmp/mysql_3306.sock -p   # Input password : &%mh9C(jsbTF

mysql -S /tmp/mysql_3307.sock -p   # Input password : YMgbgJLkB9)r

# The only way to enter each instance is to change the password and create a closed account

stay mysql5.7 The user name and password cannot be the same , Otherwise, an error will be reported .

(unknown)@localhost [(none)]> alter user user() identified by 'redhat';  # Change Password

root@localhost [(none)]> create user 'mdev'@'localhost' identified by 'mdev4admin';

root@localhost [(none)]> grant shutdown on *.* to 'mdev'@'localhost';

root@localhost [(none)]>select user,host,authentication_string from mysql.user;

+-----------+-----------+-------------------------------------------+

| user      | host      | authentication_string                     |

+-----------+-----------+-------------------------------------------+

| root      | localhost | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |

| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |

| mdev      | localhost | *947A6520F0A2FD9A631D42B10246B03F392B35C2 |

+-----------+-----------+-------------------------------------------+

root@localhost [(none)]>flush privileges;

Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]>show grants for 'mdev'@localhost;

+---------------------------------------------+

| Grants for mdev@localhost                   |

+---------------------------------------------+

| GRANT SHUTDOWN ON *.* TO 'mdev'@'localhost' |

+---------------------------------------------+

1 row in set (0.00 sec)

# Manually verify password off 3306 database

mysqladmin -S /tmp/mysql_3306.sock -p -u mdev shutdown   # Input password mdev4admin

mysqladmin -S /tmp/mysql_3307.sock -p -u mdev shutdown

# close 3307 database

mysqladmin -S /tmp/mysql_3306.sock -p shutdown

# Because you have to add -s, To get the password of the corresponding port .

[root@mysql01 ~]# my_print_defaults -s mysqld_multi mysqld3307

--mysqld=/usr/local/mysql/bin/mysqld_safe

--mysqladmin=/usr/local/mysql/bin/mysqladmin

--log=/opt/mysql/mysqld_multi.log

--user=mdev

--password=mdev4admin

--port=3307

--datadir=/data/mysql/mysql_3307/data

--tmpdir=/data/mysql/mysql_3307/tmp

--log-bin=/data/mysql/mysql_3307/logs/mysql-bin

--server-id=1003307

--socket=/tmp/mysql_3307.sock

--innodb_buffer_pool_size=500M

# So it has to be changed mysqld_multi The default script for

[root@mysql01 ~]# vi /usr/local/mysql/bin/mysqld_multi

#####################################################################

216#my $com= join ' ', 'my_print_defaults', @defaults_options, $group;

217my $com= join ' ', 'my_print_defaults -s', @defaults_options, $group;

[root@mysql01 ~]# mysqld_multi stop 3306

[root@mysql01 ~]# cat /opt/mysql/mysqld_multi.log

Stopping MySQL servers

mysqladmin: [Warning] Using a password on the command line interface can be insecure.

2016-08-21T04:50:39.315023Z mysqld_safe mysqld from pid file /data/mysql/mysql_3306/data/mysql.pid ended

[root@mysql01 ~]# mysqld_multi stop 3307

[root@mysql01 ~]# cat /opt/mysql/mysqld_multi.log       # Check to see if the shutdown was successful

Stopping MySQL servers

mysqladmin: [Warning] Using a password on the command line interface can be insecure.

2016-08-21T04:47:55.397112Z mysqld_safe mysqld from pid file /data/mysql/mysql_3307/data/mysql.pid ended

[root@mysql01 ~]# mysqld_multi start    # Nothing in the back , It means that all mysql Instance start .

[root@mysql01 ~]# mysqld_multi start 3306-3310     #3306 To 3310 All instances of are started .

[root@mysql01 ~]# ps -ef|grep mysql     # See Qidong process

[root@mysql01 ~]# cat /opt/mysql/mysqld_multi.log  # Check the log to make sure the startup is successful

Starting MySQL servers

2016-08-21T04:59:41.925014Z mysqld_safe Logging to '/data/mysql/mysql_3306/data/error.log'.

2016-08-21T04:59:41.932341Z mysqld_safe Logging to '/data/mysql/mysql_3307/data/error.log'.

2016-08-21T04:59:41.946622Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/mysql_3306/data

2016-08-21T04:59:41.952334Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/mysql_3307/data

[root@mysql01 ~]# mysql -S /tmp/mysql_3307.sock -p

Enter password:

root@localhost [(none)]>show global variables like "%innodb_buffer_pool%";

+-------------------------------------+----------------+

| Variable_name                       | Value          |

+-------------------------------------+----------------+

| innodb_buffer_pool_chunk_size       | 134217728      |

| innodb_buffer_pool_dump_at_shutdown | ON             |

| innodb_buffer_pool_dump_now         | OFF            |

| innodb_buffer_pool_dump_pct         | 25             |

| innodb_buffer_pool_filename         | ib_buffer_pool |

| innodb_buffer_pool_instances        | 1              |

| innodb_buffer_pool_load_abort       | OFF            |

| innodb_buffer_pool_load_at_startup  | ON             |

| innodb_buffer_pool_load_now         | OFF            |

| innodb_buffer_pool_size             | 536870912|  # Probably 500M, Individually defined .

+-------------------------------------+----------------+

10 rows in set (0.00 sec)

utilize mysqld_multi More articles on starting management multi instance

  1. utilize mysql_multi To manage multiple instances :

    mysql_multi The practical significance of : 1: As the number of connections goes up , Performance will degrade , Improve performance by streaming a large number of connections through multiple instances . 2: Do resource isolation 3: Sub database and sub table mysql_multi It's a script for the official management of multiple instances , utilize perl language ...

  2. MySQL Use mysqld_multi Detailed process of deploying single machine multi instance ( turn )

    With the development of hardware ,linux Multi core system is a common trend , and mysql It's single process multithreading , Therefore, the utilization of multi process is not very high , although 5.6 The version has improved a lot in this area , But it didn't reach 100%, So in order to make the most of the system ...

  3. be based on mysqld_multi Realization MySQL Multi instance configuration

    Environmental Science : operating system  CentOS7.5( already installed MySQL) Host name     localhost The local installation path is /usr/local/mysql The initial configuration of the experiment : All hosts turn off firewalls and selinux [ro ...

  4. App Examples and techniques of startup page design

    App Startup page , Also known as the flash page , Initially to ease user waiting Web/iOS/Android App It's the anxiety of data loading , Later, it was skillfully used for brand culture display by designers , Service features and functional interface familiar with the design platform , Given more abundance ...

  5. Teach you how to manage SQL Example series (1-15)

    The whole series is transferred from :51CTO ->jimshu http://jimshu.blog.51cto.com The directory and original connection are as follows : Teach you how to manage SQL example (1) Database instance Teach you how to manage SQL example (2) Service startup account ...

  6. utilize SQLiteOpenHelper To manage SQLite database ( turn )

    Reprinted from   utilize SQLiteOpenHelper To manage SQLite database http://blog.csdn.net/conowen/article/details/7306545 Android Learning notes ( ...

  7. php edition redis plug-in unit ,SSDB database , Enhanced Redis management api example

    php edition redis plug-in unit ,SSDB database , Enhanced Redis management api example SSDB It's a set of bases LevelDB Storage engine's non relational database (NOSQL), Can be used to replace Redis, More suitable for massive data storage . in addition ,ro ...

  8. MFC Start only one instance of the program

    Problem description : We may often encounter , Start only one instance of the program . That is, after a program starts , If the program is executed again , The previously opened program will be restored , Instead of opening a new program . Realization principle : utilize FindWindow/FindWi ...

  9. Single user mode starts SQL Server Example summary

      stay SQL Server In the process of database maintenance , Sometimes in some special cases, you need to start in single user mode SQL Server example . Here's a summary of how to start user mode SQL Server In several ways : 1: Command mode (sqls ...

Random recommendation

  1. pip Douban mirror use

    pip install -i https://pypi.douban.com/simple/ flask Use https Of https://pip.pypa.io/en/latest/user_gui ...

  2. Redhat linux Mount command mount

    Command format : mount [-t vfstype] [-o options] device dir among : 1.-t vfstype Specify the type of file system , It is usually not necessary to specify .mount Will automatically select the right type . ...

  3. 9 An imaginative JavaScript Physics and gravity experiments

    In this list, I picked 9 It's a physics and gravity experiment , Used to display Javascript A powerful . A few years ago , All of these experiments have to use Java or Flash To do . In these amazing examples , Personally , I prefer imitation fabrics ...

  4. jquery.tmplate Use experience

    jquery.tmplate Use experience jquery.tmpl.js, Is with the jquey In common use html Template plug-ins . The plug-in can put data into html In the template , It can render data to the page very well . The plug-in is available in this ...

  5. You should learn Python Multi version management tools Pyenv

    Catalog Pyenv brief introduction install pyenv adopt pyenv install python Various distributions pyenv command Many versions Python Management of Pyenv common problem Wiki Pyenv brief introduction First , The tool is in class linux Ring ...

  6. [vue Development record ]vue Imitation ios Native datepicker Realization

    Let's start with the renderings Now it's only developed in years , Still optimizing . I saw a pure native on the Internet js Realize the article of inertia rolling and springback   Address :https://www.cnblogs.com/ranyonsue/p/8119155.htm ...

  7. extjs +String2 +Spring Pagination under And back to json The problem of format errors

    First , Pagination   It's simple . 1 The front desk extjs data source , var shipMgrStore = Ext.create('Ext.data.Store', { model: 'App.ShipMgr.model ...

  8. springboot unit testing Injection failed Null pointer

    Write code today , stay test In the class @Autowired Inject what you want to test @Component class , But found a run will report null pointer exception java.lang.NullPointException, But it was found that new When we use this method, we can ...

  9. Check whether the browser supports cookie function

    <script> if(navigator.cookieEnabled) { document.write(" Your browser supports cookie function !"); } else{ d ...

  10. cmake practice How to install executable file in this article

    I'm learning cmake practice In chapter four , The tasks of chapter four are as follows modify Helloworld Support installation at the beginning of this section, we defined the tasks of this section as follows :1, Add a subdirectory to the project src, Used to store source code ;2, Add a subhead ...