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. |
|||
| (9 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 55: | Line 96: | ||
<syntaxhighlight lang="bash"> | <syntaxhighlight lang="bash"> | ||
$ mysqldump --master-data --databases `mysql -B -N -e "SHOW DATABASES LIKE 'foobar%'" -uroot -p` -uroot -p | $ mysqldump --master-data --databases `mysql -B -N -e "SHOW DATABASES LIKE 'foobar%'" -uroot -p` -uroot -p > dbdump.db | ||
</syntaxhighlight> | </syntaxhighlight> | ||
| Line 114: | 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 124: | 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 245: | 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]] | ||