Mysql – Proper procedure for migrating a MySQL database to another Debian machine

I would suggest that this is indeed indicative of a problem with db3. By default, mysqldump generates "extended" insert statements, containing more than one row's worth of insert per line.

INSERT INTO table_name VALUES (...), (...), (...), ...;

This is an optimization, since multiple inserts in a single statement are much faster than executing individual insert statements.

But, you can disable this behavior using the --skip-extended-insert option.

Using that option for backups isn't a good idea, since they restore much more slowly, but this option does make dump files that are much easier to read with your eyeballs and much easier to search through for a specific record using grep or a similar tool.

Dump the database using this option and then search through the file for the duplicate keys that are throwing errors and it seems likely that you'll find duplicate rows or rows with duplicate keys that should have been unique... which means underlying table trouble in db3.

I can't think of a way this could happen with InnoDB but with MyISAM it's distinctly possible.

There are a couple of other mysqldump options that might also be useful:

• --replace generates a file writing the statements as REPLACE INTO instead of INSERT INTO which would result in the duplicate key records occurring later in the file replacing the conflicting records that occurred earlier in the file, without generating an error
• --insert-ignore generates a file with the statements written as INSERT IGNORE INTO instead of INSERT INTO, which would cause the duplicate key records occurring earlier in the file to be persisted in the restored tables, as the later conflicting records would be ignored, not inserted, and would not generate an error.

Well, if you have full access to the database host you don't need to delete and re-create the password file.

According to Using Operating System Authentication section in Database Administrator's Guide logging in as an OS user which is a member of dba group on *nix or ora_dba group on Windows and connecting to an Oracle Database instance using SQL*Plus with SYSDBA or SYSOPER privileges should do the trick.

$ ssh root@orcldbxe1
[root@orcldbxe1 ~]# su - oracle
[oracle@orcldbxe1 ~]$ groups
oinstall wheel dba
[oracle@orcldbxe1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Aug 22 08:22:35 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL>

That's why it's called operating system authentication – you use operating system group. Simple and convenient, and no need to remember passwords. I always use it.

Once you're in, you can reset passwords and do other administrative tasks.

SQL> alter user spongebob identified by s3cr3t;