4,348
edits
→Copying MySQL databases on the same server: MySQL Date Format: What Datatype Should You Use? We Compare Datetime, Timestamp and INT.
→Update auto_increment value for a table: Read the auto_increment of a table |
→Copying MySQL databases on the same server: MySQL Date Format: What Datatype Should You Use? We Compare Datetime, Timestamp and INT. |
||
| (4 intermediate revisions by the same user not shown) | |||
| Line 23: | Line 23: | ||
<source lang="mysql"> | <source lang="mysql"> | ||
mysql> revoke all privileges on dbcloud.* from 'dbacc'@'localhost'; | 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> | </source> | ||
| Line 146: | Line 163: | ||
mysql> select `auto_increment` from information_schema.tables where table_schema = 'iechomain' and table_name = 'user_sites'; | mysql> select `auto_increment` from information_schema.tables where table_schema = 'iechomain' and table_name = 'user_sites'; | ||
</source> | </source> | ||
Update the auto_increment of a table. | |||
<source lang="mysql"> | <source lang="mysql"> | ||
| Line 160: | 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 281: | 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]] | ||