4,348
edits
→Copying MySQL databases on the same server: MySQL Date Format: What Datatype Should You Use? We Compare Datetime, Timestamp and INT.
→Copying MySQL databases on the same server: MySQL Date Format: What Datatype Should You Use? We Compare Datetime, Timestamp and INT. |
|||
| (10 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) == | ||
| Line 37: | Line 78: | ||
<syntaxhighlight lang="mysql"> | <syntaxhighlight lang="mysql"> | ||
mysql> FLUSH TABLES WITH READ LOCK; | mysql> FLUSH TABLES WITH READ LOCK; | ||
<syntaxhighlight | </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. | 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. | ||
| Line 52: | Line 93: | ||
=== Create a snapshot using mysqldump (on master) === | === Create a snapshot using mysqldump (on master) === | ||
While the first session with <code>FLUSH TABLES</code> is still hanging there, | 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"> | |||
$ 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"> | <syntaxhighlight lang="bash"> | ||
$ mysqldump --all-databases --master-data > dbdump.db | $ mysqldump --all-databases --master-data -uroot -p > dbdump.db | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| Line 108: | Line 155: | ||
mysql> start slave; | 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 == | == Checking the status of the replication setup == | ||
| Line 118: | Line 179: | ||
* ''Last tested on Ubuntu 16.04.01 LTS (xenial)'' | * ''Last tested on Ubuntu 16.04.01 LTS (xenial)'' | ||
< | <source lang="bash"> | ||
$ mysql -B -uusername -ppassword --disable-column-names --execute "show databases" | $ mysql -B -uusername -ppassword --disable-column-names --execute "show databases" | ||
</ | </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 239: | 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]] | ||