Han Wiki

MySQL: Difference between revisions

Page Discussion

MySQL (view source)

Revision as of 21:36, 25 December 2018

6,801 bytes added ,  25 December 2018
→‎Copying MySQL databases on the same server: MySQL Date Format: What Datatype Should You Use? We Compare Datetime, Timestamp and INT.
← Older edit
→‎Get master's binary log coordinate: Create a snapshot using mysqldump
→‎Copying MySQL databases on the same server: MySQL Date Format: What Datatype Should You Use? We Compare Datetime, Timestamp and INT.
 
(12 intermediate revisions by the same user not shown)
Line 1: Line 1:
<!--__FORCETOC__-->
<!--__FORCETOC__-->
== Removing access from users ==
Show all grants for a user.
<source lang="mysql">
mysql> show grants for 'webuser'@'localhost';
</source>
Show a list of users for db, tables, columns, or processes.
<source lang="mysql">
mysql> use mysql;
mysql> select user,host from db where db='db_name';
mysql> select user,host from tables_priv where db='table_name';
mysql> select user,host from columns_priv where db='columns_name';
mysql> select user,host from procs_priv where db='procs_name';
</source>
Revoke all privilege from a database.
<source lang="mysql">
mysql> revoke all privileges on dbcloud.* from 'dbacc'@'localhost';
</source>
== Suppress password on the CLI warning ==
When logging into the MySQL console with password you get a warning like this:
<source lang="bash">
$ mysql -uusername -ppassword -Dmydb
</source>
<code>mysql: [Warning] Using a password on the command line interface can be insecure.</code>
You can suppress this by using <span class="package">mysql_config_editor</span> utility.
<source lang="bash">
$ mysql_config_editor set --login-path=local --host=localhost --user=username --password
$ mysql --login-path=local  -Dmydb -e "statement"
</source>


== Setting up a Replication (Master-Slave) ==
== Setting up a Replication (Master-Slave) ==
* ''Last tested on Ubuntu 14.04.05 LTS (trusty) as the master, and Ubuntu 16.04.02 LTS (xenial) as the slave.''
In this procedure, the master is assumed to have tables with InnoDB with data.  Some parameters and commands are specifically for InnoDB engines.
My master happens to be MySQL and the slave happens to be running MariaDB.  This shouldn't cause any issues as MariaDB is a drop-in replacement for MySQL as long as you DO NOT use GTID.  However, if both are same types the use of GTID is highly recommended.
The slave is a newly installed Ubuntu server installation.


=== Setting the replication master config ===
=== Setting the replication master config (on master) ===
* ''Last tested on Ubuntu 14.04.05 LTS (trusty)''


Activate the binary log on the master. <code>innodb_flush_log_at_trx_commit</code> and <code>sync_binlog</code> added for greatest possibility durability and consistency in a replication setup using <span class="package">InnoDB</span> with transactions.  
Activate the binary log on the master. <code>innodb_flush_log_at_trx_commit</code> and <code>sync_binlog</code> added for greatest possibility durability and consistency in a replication setup using <span class="package">InnoDB</span> with transactions.  
Line 16: Line 63:
</syntaxhighlight>
</syntaxhighlight>


=== Create a user for replication ===
=== Create a user for replication (on master) ===


Grant <span class="package">REPLICATION_SLAVE</span> privilege to the new user.
Grant <span class="package">REPLICATION_SLAVE</span> privilege to the new user.
Line 25: Line 72:
</syntaxhighlight>
</syntaxhighlight>


=== Get master's binary log coordinate ===
=== Get master's binary log coordinate (on master) ===


On the master stop commit operations on InnoDB table and read the coordinates.
On the master stop commit operations on InnoDB table and read the coordinates. <code>FLUSH TABLES WITH READ LOCK;</code> command needs to be run in one session and left at that.  Running any other commands after will potentially unlock the tables again.


<syntaxhighlight lang="mysql">
<syntaxhighlight lang="mysql">
mysql> FLUSH TABLES WITH READ LOCK;
mysql> FLUSH TABLES WITH READ LOCK;
</syntaxhighlight>
Open another terminal session on the same server, and get the binary log position.  Write down the value for File and Position somewhere.  In this example, it would <span class="package">mysql-bin.000003</span> and <span class="package">73</span>, respectively.
<syntaxhighlight lang="mysql">
mysql> SHOW MASTER STATUS;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
+------------------+----------+--------------+------------------+
Line 39: Line 91:
</syntaxhighlight>
</syntaxhighlight>


=== Create a snapshot using mysqldump ===
=== Create a snapshot using mysqldump (on master) ===


This assuming there are existing data using InnoDB engines.
While the first session with <code>FLUSH TABLES</code> is still hanging there, make a dump of the databases. This assumes there are existing data in InnoDB tables, and you are only backing up databases that have names starting with ''foobar''.  Note that you will be entering the password twice for root.


<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
$ mysqldump --all-databases --master-data > dbdump.db
$ mysqldump --master-data --databases `mysql -B -N -e "SHOW DATABASES LIKE 'foobar%'" -uroot -p` -uroot -p > dbdump.db
</syntaxhighlight>
 
The next command is for dumping EVERYTHING. I didn't use this, but it's put here merely as a reference. I would not use this if your master & slave are of different versions. Mine happens to be MySQL & MariaDB, so it's a big no no.
 
<syntaxhighlight lang="bash">
$ mysqldump --all-databases --master-data -uroot -p > dbdump.db
</syntaxhighlight>
 
After this is done, you can unlock the tables again.
 
<syntaxhighlight lang="mysql">
mysql> unlock tables;
</syntaxhighlight>
 
Copy <code>dbdump.db</code> to the slave server using scp or whatever else you use for server-to-server file copying.
 
=== Setting the replication slave configuration (on slave) ===
 
This procedure can be applied to multiple slave servers.  Just make sure that you have different <span class="package">server-id</span> value for each slave server.  You may also want to turn binary logging on for the slave if you're planning to switch off the master in the future and turn the slave into a new master for other slaves.
 
The file can be located as <span class="path">/etc/mysql/mysql.conf.d/mysqld.cnf</span> or <span class="path">50-server.cnf</span>.
 
<syntaxhighlight lang="cfg">
[mysqld]
server-id=2
</syntaxhighlight>
 
Restart mysql daemon. On mine, the command is <code>systemctl restart mysql.service</code>.  Same with MariaDB, if you have the full package installed.
 
=== Setting the master config on the slave (on slave) ===
 
Configure the slave with necessary connection information. Make sure MASTER_LOG_FILE and MASTER_LOG_POS match what you had written down above EXACTLY.
 
<syntaxhighlight lang="mysql">
mysql> CHANGE MASTER TO
    ->  MASTER_HOST='<em>master_host_name_or_ip_address</em>',
    ->  MASTER_USER='repluser',
    ->  MASTER_PASSWORD='mypassword',
    ->  MASTER_LOG_FILE='mysql-bin.000003',
    ->  MASTER_LOG_POS=73,
    ->  MASTER_CONNECT_RETRY=10;
</syntaxhighlight>
 
=== Load the existing data on the slave ===
 
Replace <code>~/dbdump.db</code> with the full path to the same file.
 
<syntaxhighlight lang="mysql">
mysql> source ~/dbdump.db
</syntaxhighlight>
 
=== Start the slave replication process ===
 
Once loading of the data completes, you can start the slave replication.
 
<syntaxhighlight lang="mysql">
mysql> start slave;
</syntaxhighlight>
</syntaxhighlight>
== Update auto_increment value for a table ==
Read the auto_increment of a table.
<source lang="mysql">
mysql> select `auto_increment` from information_schema.tables where table_schema = 'iechomain' and table_name = 'user_sites';
</source>
Update the auto_increment of a table.
<source lang="mysql">
mysql> alter table users auto_increment = 388;
</source>
== Checking the status of the replication setup ==
If you're interested in checking the status, run <code>show slave status;</code> on the slave.
On the master, you can run <code>show master status;</code>, or <code>show processlist</code> to check the running processes.


== List all database without the border ==
== List all database without the border ==
* ''Last tested on Ubuntu 16.04.01 LTS (xenial)''
* ''Last tested on Ubuntu 16.04.01 LTS (xenial)''


<syntaxhighlight lang="bash">
<source lang="bash">
$ mysql -B -uusername -ppassword --disable-column-names --execute "show databases"
$ mysql -B -uusername -ppassword --disable-column-names --execute "show databases"
</syntaxhighlight>
</source>
 
== Restoring a table from .frm and .ibd files ==
 
Assuming you already have a running database, this demonstrates a restore of a table into a temporary database.
 
# Create a temporary database
#: Use a <code>create database</code> or other means to create a temporary database to use as to restore the table.
# Restore the structure of the table
#: Use '''mysqlfrm''' tool to extract ''create table'' script.
#: <code>mysqlfrm -server=root:password@localhost -port=3311 "path/to/mytable.frm" > "path/to/recovered_mytable.sql"</code>
# Recreate the table
#: Create the table using the generated script, or previously existing script that matches the backup structure.
# Replace .ibd file.
#: Remove the newly created .idb file by executing the following: <source lang="mysql">> ALTER TABLE mytablename DISCARD TABLESPACE;</source>
#: Copy the restored .ibd file into the folder that contains the .ibd file.
# Reactivate the table
#: Re-establish the link. <source lang="mysql">> ALTER TABLE mytablename IMPORT TABLESPACE;</source>
#: You can usually ignore the warnings (sometimes even errors) that show up.


== mysqldump returning Errcode 13 ==
== mysqldump returning Errcode 13 ==
Line 171: Line 318:


That should do it!
That should do it!
= Links =
[https://www.vertabelo.com/blog/technical-articles/what-datatype-should-you-use-to-represent-time-in-mysql-we-compare-datetime-timestamp-and-int MySQL Date Format: What Datatype Should You Use? We Compare Datetime, Timestamp and INT.]


[[Category:System administration]]
[[Category:System administration]]
Retrieved from "https://wiki.michaelhan.net/Special:MobileDiff/1093...2775"