Procedure:MySQL Database Transfers: Difference between revisions

From OACISS Systems Wiki
Jump to navigation Jump to search
(Created page with "= Chymera X11 setup info = This page describes how to get the 8K display output from Chymera in the Streisenger 472 conference room. = Problems not yet resolved = == Autos...")
 
No edit summary
 
Line 1: Line 1:
= Chymera X11 setup info =
This procedure describes how to transfer data across SQL databases, or more generally from a MySQL DB to another DB (including MySQL)


Step 1 is to get the data out of the original DB. The exact command may depend on what you want; Typical examples spanning most scenarios are:
* "mysqldump -uUSER -pPASS --all-databases > databaseFile" Stores everything in a format suitable for initializing a blank DB, including user tables
* "mysqldump -uUSER -pPASS --databases db1 db2 ... > databaseFile" Like above but only stores the named set of databases db1, db2, ..., dbN.
* "mysqldump -uUSER -pPASS --all-databases --add-drop-table --add-drop-database --add-locks > databaseFile" Will instruct destination system to impose a Pax Romana before resettling the land. Add-locks will lock tables while adding data and should make the upload onto the destination box go faster.


This page describes how to get the 8K display output from Chymera in the Streisenger 472 conference room.
mysqldump HALTS in the event of error and WILL leave you an incomplete dump (not that it'll actually say "don't use this, it's incomplete" - beware)


= Problems not yet resolved =
Step 2: scp databaseFile USER@NEWMACHINE:~/


== Autostupid monitor rearrangement ==
Step 3 is uploading the database dump file into the new database. The dump file is a bunch of text SQL statements strung together, not a heaving mass of binary data, so in principle you can upload to anything that speaks SQL. On a virgin MySQL, one would probably use "mysql -uroot -p < databaseFile" since by default mysql has no access restrictions. If the original mysql's user DB isn't included in databaseFile you'd best either add users & restrictions now and/or check that usernames/permissions are compatible across the machines. On an existing DB, "mysql -uUSER -pPASS < databaseFile" will perform the upload (assuming that USER has the privileges to do whatever is implied by the original dump, such as create tables/DBs/users and possibly drop them and lock tables.


I have searched far and wide but have yet to find the magical xorg.conf incantation which will tell the system "never attempt to automatically rearrange the displays, period, for any reason."
This all completed successfully, a "/etc/init.d/mysqld restart" will make sure all the new hotness is running, and nothing old and busted is gumming up the works. There's some command to have mysql flush everything online too.


If a panel is
'man mysqldump' and 'man mysql' for more information and many more options if desired.
* Switched on
* Even begins receiving a video stream on a different HDMI port than currently in use (e.g. a laptop is connected to a different port)
, the panel will send some sort of signal via HDMI to Chymera. Even if Chymera is not currently set to use that panel (e.g. it is using the left two displays, or only one), X or Gnome (I'm not sure which) will take the signal and respond by more or less completely trashing everything about the current monitor arrangement. Every time it happens.
 
== Strange DisplayPort problem ==
 
There is, apparently, some form of defect in DisplayPort wherein if the GPU decides the monitor went to sleep, it will never, ever output to that port again until the connection is physically unplugged and replugged. If this occurs (e.g. nvidia-settings shows everything is arranged right but one panel is blank), you really do have no recource but to physical pull the adapter from the cards (not the hdmi cable from the adaptor) and replug.
 
= Physical layout =
 
The monitors are known to the system in the following order:
 
+------+------+
| DP-5 | DP-4 |
+------+------+
| DP-6 | DP-7 |
+------+------+
 
On paper the following xrandr command should throw all panels on and into the correct position, resolution and orientation:
 
xrandr --fbmm 1440x810 \
        --output DP-4 --mode 3840x2160  --pos +3840x+0  --reflect xy \
        --output DP-5 --mode 3840x2160  --pos +0x+0    --reflect xy \
        --output DP-6 --mode 3840x2160  --pos +0x+2160  \
        --output DP-7 --mode 3840x2160  --pos +3840x+2160
 
In reality, unless all four screens are already on, it is slightly less reliable than a desperate meth addict and I've had it mangle the arrangement so bad that using nvidia-settings' expert mode was necessary.
 
The only method I know of which is more than fractionally reliable to arrange them is to manually set this up in nvidia-settings:
* Set all panels to 3840x2160
* Set DP-4 and DP-5 to "reflect XY"
* Drag them in the layout box to match the 2x2 arrangement above
 
= Audio not working? =
 
Chymera can play audio from the monitors via HDMI. If this is not working:
 
* Click the speaker icon on the launch bar
* Does it show no output hardware other than "Dummy?"
 
Then the problem is that you aren't in the system's "audio" group (having been authenticated by LDAP which doesn't know about per-system requirements) and are being denied access to /dev/snd (whose devices are owned by root:audio and are chmod 660).
 
If you do not have superuser access, you will have to ask for your user name to be added to the audio group in /etc/group on Chymera.

Latest revision as of 02:04, 4 February 2020

This procedure describes how to transfer data across SQL databases, or more generally from a MySQL DB to another DB (including MySQL)

Step 1 is to get the data out of the original DB. The exact command may depend on what you want; Typical examples spanning most scenarios are:

  • "mysqldump -uUSER -pPASS --all-databases > databaseFile" Stores everything in a format suitable for initializing a blank DB, including user tables
  • "mysqldump -uUSER -pPASS --databases db1 db2 ... > databaseFile" Like above but only stores the named set of databases db1, db2, ..., dbN.
  • "mysqldump -uUSER -pPASS --all-databases --add-drop-table --add-drop-database --add-locks > databaseFile" Will instruct destination system to impose a Pax Romana before resettling the land. Add-locks will lock tables while adding data and should make the upload onto the destination box go faster.

mysqldump HALTS in the event of error and WILL leave you an incomplete dump (not that it'll actually say "don't use this, it's incomplete" - beware)

Step 2: scp databaseFile USER@NEWMACHINE:~/

Step 3 is uploading the database dump file into the new database. The dump file is a bunch of text SQL statements strung together, not a heaving mass of binary data, so in principle you can upload to anything that speaks SQL. On a virgin MySQL, one would probably use "mysql -uroot -p < databaseFile" since by default mysql has no access restrictions. If the original mysql's user DB isn't included in databaseFile you'd best either add users & restrictions now and/or check that usernames/permissions are compatible across the machines. On an existing DB, "mysql -uUSER -pPASS < databaseFile" will perform the upload (assuming that USER has the privileges to do whatever is implied by the original dump, such as create tables/DBs/users and possibly drop them and lock tables.

This all completed successfully, a "/etc/init.d/mysqld restart" will make sure all the new hotness is running, and nothing old and busted is gumming up the works. There's some command to have mysql flush everything online too.

'man mysqldump' and 'man mysql' for more information and many more options if desired.

Retrieved from "https://systems.nic.uoregon.edu/internal-wiki/index.php?title=Procedure:MySQL_Database_Transfers&oldid=3014"