39
The slickest way to shutdown mysql when it does that is simply to run
mysqladmin -uroot -p -h127.0.0.1 --protocol=tcp shutdown
Here is why:
The mysql service file (/etc/init.d/mysql) relies on the presence of the socket file. Historically speaking, going way back to MySQL 4.0, the socket file sometimes disappears inexplicably. This hampers a standard ...
answered Mar 6 '13 at 16:06
RolandoMySQLDBA
162k2626 gold badges268268 silver badges452452 bronze badges
22
Brent listed some invalid reasons for stopping the service, but there are valid reasons too:
Restart required by a service pack or other update
Certain configuration changes (e.g. service account change, hardware changes, instant file initialization, this list could go on for weeks)
In a cluster, a restart to force a failover or applying a rolling patch
...
answered Jan 7 '17 at 21:58
Aaron Bertrand
170k2323 gold badges358358 silver badges565565 bronze badges
19
Because they think there's a memory problem - SQL Server uses all of the memory available to it, up to its max memory setting (and even beyond.) Unknowing folks go into Task Manager, see SQL Server using a lot of memory, and think, "There must be a memory leak - I'll stop and restart SQL Server, and see what happens." Sure enough, that frees up a lot of ...
15
You don't have to be fancy/worried or scared when you are restarting sql server.
Just make sure that you dont have any long running transactions. Best is to restart sql server using console or shutdown command during a low/minimum activity period also called maintenance window to minimize impact on your business.
If you have any DR setup and you dont want ...
8
You can use the dbstart/dbshut scripts which come with an Oracle install. They are available under $ORACLE_HOME/bin.
After a fresh install you have to edit the /etc/oratab file:
# cat /etc/oratab
# format: $ORACLE_SID:$ORACLE_HOME:N|Y
my_sid:/home/juser/app/juser/product/11.2.0/dbhome_1:N
# sed -i 's/:N$/:Y/' /etc/oratab
# grep my_sid /etc/oratab
my_sid:/...
6
Yes, it's possible.
SQL> alter database close;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> alter database dismount;
Database altered.
SQL> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01507: database not mounted
5
This is all detailed exhaustively on this page.
Start, Stop, Pause, Resume, Restart SQL Server Services
Being that your question specifically asks "are there any recommended by Microsoft" I'm inclined to think that this is counter-productive to have this discussion here. The article their details the process through
Using either
command line
Powershell,
...
answered Jan 16 '18 at 20:44
Evan Carroll
48.1k2424 gold badges159159 silver badges350350 bronze badges
5
It sounds as if your question is less about "how to" shut down MySQL and more about why yours is shutting down so slowly.
In my answer to a similar question, I offered some suggestions for a smooth restart, which help by reducing the amount of activity that has to happen after you request that MySQL begin the shutdown process.
If you are not a frequent ...
4
Linux's Viewpoint
You should run this Linux command
history | grep mysqladmin
This will let you see if anyone ran a shutdown from within the server. Note that this will not let you see remote mysqladmin shutdowns. Perhaps running tcpdump and locating the word mysqladmin or shutdown might help.
MySQL's Viewpoint
Shutdown commands do not exist from command ...
answered Apr 22 '13 at 16:27
RolandoMySQLDBA
162k2626 gold badges268268 silver badges452452 bronze badges
4
Without seeing any additional information, here is my best guess why MySQL is trying to shutdown for a long time: I suspect mysqld no longer has a socket file.
About 1.5 years ago, I answered mysql restart issue after move database. I learned that mysqld depends on the presence of a socket file. If there is no socket, mysqld just draws lots of dots on the ...
answered Jan 10 '17 at 22:08
RolandoMySQLDBA
162k2626 gold badges268268 silver badges452452 bronze badges
4
The documentation tells you to send a signal to the master postgres process, or to let pg_ctl do this.
In SQL, you can extract the PID of the master process from pg_read_file('postmaster.pid'), but pg_cancel_backend() does not accept this PID.
However, you should be able to execute these commands with COPY (depending on what rights the postgres OS user has)...
3
Ideally you can shutdown using
/etc/init.d/mysqld stop
I cannot add comments so requesting to post what do you see in error log when you do
mysqladmin shutdown
About kill commands please note that mysqld_safe is wrapper script which will start mysqld upon killing... so you should kill mysqld_safe first followed by mysqld. ( not a suggested way though)....
3
It helps to understand what 'mount mode' means:
Before DB is mounted, instance is up (SGA and other memory structures are allocated but DB and its files etc have not been verified yet. In mount mode, the controlfile will be read, it will make sure all logfiles/datafiles etc are available and ready to go. This is essential maintenance. Then, after mounting, ...
3
If you want to shutdown MySQL effectively, you need to run several things
@echo off
rem
rem Flush Everything and its Grandmother
rem
echo "Stopping MySQL Service"
"C:\host\MariaDB\bin\mysql.exe" -u root -prootpass -ANe"SET GLOBAL innodb_fast_shutdown=0; STOP SLAVE; FLUSH BINARY LOGS; FLUSH TABLES"
"C:\host\MariaDB\bin\...
answered Oct 12 '16 at 3:05
RolandoMySQLDBA
162k2626 gold badges268268 silver badges452452 bronze badges
3
Not exactly when it comes to shutting down and preventing DB corruption. MS SQL Server is a very mature product and the odds of causing a corruption issue by a simple 'shutdown' would be a edge scenario. You're much more likely to cause corruption by not running CHECK DB or having checksum validation set on your DB.
Perhaps having external tools ...
3
I ran into this problem on a managed system. Took me a while to spot the very obvious cause, which is that the system as a whole had gone down for scheduled maintenance -- I guess because an updated kernel package was available.
So obvious!
The fix was of course to change the system configuration so that MySQL was automatically started when the system ...
3
SHUTDOWN
MySQL 5.7 now features the SHUTDOWN command, which requiresthe SHUTDOWN privilege.
SHUTDOWN METHOD #1: Within mysql client
mysql> SHUTDOWN;
SHUTDOWN METHOD #2: From OS using mysql client
MYSQL_HOST=127.0.0.1
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -P3306 --protocol=tcp"
mysql ${...
answered Feb 5 '18 at 17:38
RolandoMySQLDBA
162k2626 gold badges268268 silver badges452452 bronze badges
3
You can set the service property to restart it (service properties --> recovery --> first / second / third failure and have a delay between each restarts e.g. 1 min). You should also, have the service set to automatic delayed start.
Alternatively, you can use powershell and schedule it in task manager to check if the service is running or not. If not ...
3
I came across similar problem and tried this Stack Overflow answer by Fabrizio Valencia. It worked.
Stop there! Please do not delete anything! First try to do what I did.
OK, none of the listed answers here solved my problem, even, some of
the answers were making me lost all my db data. So I did exactly what
the error message in XAMPP message panel told me ...
2
It's probable MySQL is not purely locked up, but is doing cleanup activities (rollbacks, etc.) when shutting down. If you don't let it do all that when shutting down, often you'll have to wait when starting up.
Here's something to look at: Shutdown mysql in one terminal window while watching the error log (tail -f [yourerror.log]) in another terminal window....
2
When you issue service mysql stop, a lot more happens than just cutting off DB Connectivity. The link in the comment from @ethrbunny already explains what things happens.
I would like to focus on one particular aspect: The InnoDB Buffer Pool. InnoDB has to flush the InnoDB Buffer Pool's dirty pages. If you want to know how much, run this before shutdown:
...
answered Apr 23 '13 at 16:31
RolandoMySQLDBA
162k2626 gold badges268268 silver badges452452 bronze badges
2
If you would like to manually control this, here are some suggestions
Login to Window Command Line Shell as Administrator and run this
C:\> net stop mysql
When the prompt comes back, MySQL is down.
If you shutdown Windows, the only evidence for MySQL's shutdown would be in the error log.
EXAMPLE
On my laptop
I have MySQL 5.5.37 for Windows
It's been up ...
answered Jan 9 '15 at 17:53
RolandoMySQLDBA
162k2626 gold badges268268 silver badges452452 bronze badges
2
It seems like the obvious answer is that you can't stop a shutdown that is happening, but you can do either a shutdown immediate or shutdown abort from a different session if you did a shutdown normal. As long as you have enough online redo log groups and each group has a log that is large enough, then you can recover from either a shutdown immediate or ...
2
I'm pretty sure the recovery process is done on each database everytime the instance start. (and in other scenario as well, like an AG failover for example)
If it was stopped in a clean way, there will be no open transaction to roll foward or roll back so the recovery will be done faster.
1
One reason could be is that you bought new hardware and migrated the databases to this new server. You now are shutting down this sql server instance on the old box (together with the box itself) since you want to make sure nobody connects to it anymore
You moved to the cloud, the on prem box is not needed anymore, it is shut down, reformatted and ...
1
A valid reason is when there is other software running on the same server that needs some of the memory SQL server has, but that only runs a few times a month.
For example my wife (an accountant who wishes to know as little (and no less) about SQL server that is needed to do her job) has a SQL server based system used by 3 people including her to process ...
1
this is surely a resources or limits problem.
For the first check if you have RemoveIPC enabled (Man for login.conf
If it does not solve, review the Database Quick Installation Guide in the sections Configuring Kernel Parameters and Resource Limits and Creating Required Operating System Groups and Users
Regards
Giova
1
You may not be aware of this, but the MySQL service is designed to be restarted automatically.
There is a program called mysqld_safe. Its job is to do the following:
STEP 01 : start mysqld
STEP 02 : check return value of mysqld
STEP 03 : If return value is zero, mysqld_safe terminates normally
STEP 04 : mysqld is restarted due to abnormal termination of ...
answered Jun 16 '15 at 15:39
RolandoMySQLDBA
162k2626 gold badges268268 silver badges452452 bronze badges
1
Try stopping the mysql server, instead of killing the process:
sudo /etc/init.d/mysql stop
1
MySQL can run as a service and I hope that windows is stopping the services gracefully or you are likely to have more problems outside of MySQL. If you want to be careful, stop the service by hand. This should allow MySQL to flush all buffers before stopping. I would guess from your comment that you're getting corruption that you are using the MyISAM engine; ...
Only top voted, non community-wiki answers of a minimum length are eligible
Related Tags
shutdown × 47mysql × 25
oracle × 9
sql-server × 5
postgresql × 4
startup × 4
mysql-5.5 × 3
windows × 3
errors × 3
log × 3
database-design × 2
replication × 2
mongodb × 2
innodb × 2
linux × 2
mysql-5.7 × 2
transaction × 2
audit × 2
mac-os-x × 2
crash × 2
instance × 2
sql-server-2008-r2 × 1
performance × 1
sql-server-2014 × 1
backup × 1