[RESOLVED] mysql 8.0.36 now wont run after I stopped service and did some things...

[sdowney1]

I stopped the service, copied over a table in one of my databases I made.
Tried to start service, wont run, windows says service started and then stopped.

What I did is

at mysql command prompt
use booksgood;

then drop table circdeweydetail;

then ran my program to create the missing table circdeweydetail

program made the table in booksgood database, of course its an empty table.

Went to file manager tried to delete table manually, said file in use.
shut down mysql80 server in windows services.

copied over a full table called circdeweydetail from a working booksgood database into the shutdown mysql80 server's datafolder for that database booksgood.

then went to restart mysql80 in services, it attempts to restart then says it can't
rebooted laptop
tried again, still wont start.

OK, so why wont it start mysql80 now???

Windows 11 message is

The mysql80 service on local computer started and then stopped. Some services stop automatically if they are not in use
by other services or programs.

[sdowney1]

LOL!!!!

totaled the instance.
I removed the service using the sc command

Then I used the installed Mysql installer-Community app in windows 11 to reconfigure the instance for 8.0.36
It installed the service and is up again.

Now have to migrate the booksgood database over the lan back to the laptop mysql 8.0.36 server

Wow this thing is fragile, IMO.

I cannot say solved, only resolved, as I don't know how what I did would crash the mysql80 server so it can not start again!

Of course this is just experimenting, but if I was a production database admin, I would either know better or be fired!
I suppose!

[sdowney1]

This is needed to migrate data using the wizard

I am putting this here for when I forget how to do this.

on the target machine running 8.0.36 server need to create a user that can do the job of writing all the tables with data or migration will fail

so log into the 8.0.36 server

like mysql -u root -p

then create root for the lan, I just used %, but can use the lan ip such as '192.168.1.%' instead of '%' etc....

create user root@'%' identified by 'root';

grant all on *.* to root@'%';

then the migration using workbench succeeds and it may stop responding during the process too.

I checked and all the tables and data is now working on the 8.0.36 server on the laptop and can be accessed across my lan using the root user.

[sdowney1]

mysql> create user root@'%' identified by 'root';
Query OK, 0 rows affected (0.01 sec)

mysql> create user root@'%' identified by 'root';
ERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'%'
mysql> grant all on *.* to root@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> use booksgood;
Database changed
mysql> show tables;
+-----------------------+
| Tables_in_booksgood |
+-----------------------+
| authordata |
| barcodedata |
| bookdata |
| booksearchtable |
| calldata |
| checkout |
| checkouthistory |
| circdata |
| circdeweydetail |
| deweyhave |
| iddata |
| lmjo7450320134047306 |
| lmof4173361075314214 |
| lmov6057335744463413 |
| lmuc86267024340655 |
| lmue1282081014729992 |
| lmyt4316767465777327 |
| loccustomhave |
| locdata |
| locgeneralcustomhave |
| locgeneralhave |
| lochave |
| locsubclasscustomhave |
| locsubclasshave |
| marcsearchtable |
| marcwrite196205259 |
| marcwrite227166176 |
| marcwrite370654882 |
| missextra |
| patrondata |
| patronsearchtable |
| pnamedata |
| psqlquerystore |
| sfav576227916183651 |
| sfay5017140518276183 |
| sfay5284779674262695 |
| sfca5948384412775747 |
| sfdb9962208878032949 |
| sfec1906178603367277 |
| sfec9459431773646045 |
| sfeg3218349823968346 |
| sfel7100796703635036 |
| sfep1024790416191088 |
| sfez263575323561069 |
| sffz8679816733964657 |
| sfga5521247396187998 |
| sfgi9168209443941142 |
| sfgl18438995447687 |
| sfgx2117544423966853 |
| sfhc2134760624264506 |
| sfhc3569544563379878 |
| sfhf1778767714093276 |
| sfhm4260775453933077 |
| sfio1131502996185682 |
| sfjc5899817953804188 |
| sfjs5900043852440435 |
| sfmj2590535292991328 |
| sfms8238284003382887 |
| sfnt8371340643963309 |
| sfoj434931524475092 |
| sfok20807993842243 |
| sfom606282362241317 |
| sfom6072285783625598 |
| sfpa9999704963936286 |
| sfpn4724629536180503 |
| sfpu8123537903567538 |
| sfqk8219306473935459 |
| sfql932088733396052 |
| sfqo6514180313929087 |
| sfus5636952523239663 |
| sfvg31529337235892 |
| sfvs7384541643958883 |
| sfxv7942860733685155 |
| sfyk908463538396554 |
| sfyt6913965953929889 |
| sfyv1581318987483234 |
| sfzp5490499743939648 |
| sfzx7644398813556796 |
| sqlquerystore |
| storedlists |
| storedusersearch |
| table_name_new |
| titledata |
| unbookdata |
| unpatrondata |
| usertable |
+-----------------------+
86 rows in set (0.00 sec)

mysql> select * from usertable;
+----+-----------+-----------+-----------------------+---------------------+---------------------+-------------+
| Id | Myuser | password | Priveliges | TS | UserDate | MyName |
+----+-----------+-----------+-----------------------+---------------------+---------------------+-------------+
| 47 | tdowney | UF | p | 2015-09-06 12:25:30 | 2015-09-06 12:25:30 | Mrs. Downey |
| 51 | Kristin | T^ZNTTRLQ | a | 2007-07-09 09:30:13 | 2007-07-09 09:30:31 | |
| 53 | MrsHedger | P[WQ | a | 2013-04-16 15:39:39 | 2013-04-16 15:39:39 | |
| 54 | MrsKersey | P[WQ | p | 2013-04-16 14:36:42 | 2013-04-16 14:36:42 | |
| 55 | MrsJones | P[WQ | p | 2013-04-16 14:37:31 | 2013-04-16 14:37:31 | |
| 56 | MrsCowley | P[WQ | p | 2013-08-27 10:29:53 | 2013-08-27 10:29:53 | |
| 57 | New | WD | P | 2024-03-16 12:51:57 | 2024-03-16 12:51:55 | |
+----+-----------+-----------+-----------------------+---------------------+---------------------+-------------+
7 rows in set (0.00 sec)

mysql>

[sdowney1]

And I have tested mysqldump of 5.7 to getting database on mysql 8.0.36 on the same machine running both servers and that works fine and is quicker than using mysql workbench.

Next will test dump from 8 to back on 8

vid of how this is done
https://www.youtube.com/watch?v=oKmBvO8rUdo&t=168s

edit, yeah it worked

example

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqldump -u root -p --port=3307 booksgood > backup.sql
Enter password: ****

Then create an empty database corp2 on the target server's data folder

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u root -p --port=3307 corp2 < backup.sql
Enter password: ****

And put this line in a bat file to create a backup and could be automated to run using task scheduler.
Just change the user, password and database name

"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqldump.exe" -u root -proot --databases booksgood --hex-blob > C:\mybackup.sql